Re: Subquery problems

On Thu, 21 Jun 2007 14:46:49 -0300
Ranieri Mazili <ranieri(dot)oliveira(at)terra(dot)com(dot)br> wrote:

Hi,

I noticed that if DIV_MES = 0 (= NULL) then the previous query
didn't go well. I rewrite the query. It's including a check statement
to replace NULL with 1.
At first, you need to create a table of months.

--
Masaru Sugawara

create table tbl_month (p_month integer);
insert into tbl_month values (1);
insert into tbl_month values (2);
insert into tbl_month values (3);
insert into tbl_month values (4);
insert into tbl_month values (5);
insert into tbl_month values (6);
insert into tbl_month values (7);
insert into tbl_month values (8);
insert into tbl_month values (9);
insert into tbl_month values (10);
insert into tbl_month values (11);
insert into tbl_month values (12);

select
C.id_production_area,
B.id_machine_type,
A.h_month as month,
max(A.n) as div_mes,
cast((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/max(A.n) as integer)
from (select *
from (select *,
extract(month from h1.head_count_date) as h_month
from head_count as h1
where extract(year from h1.head_count_date)
= extract(year from current_date)
) as h2,
(select t1.p_month, coalesce (p1.n, 1) as n
from tbl_month as t1 left outer join
(select extract(month from production_date) as m,
count(distinct p0.production_date) as n
from production as p0
where extract(year from production_date)
= extract(year from current_date)
group by extract(month from production_date)
) as p1 on (t1.p_month = p1.m)
) as p2
where h2.h_month = p2.p_month
) as A, machine B, machine_type C
where A.id_machine = B.id_machine
and B.id_machine_type = C.id_machine_type
group by C.id_production_area, B.id_machine_type, A.h_month
order by C.id_production_area, A.h_month, A.h_month DESC;

> Hello,
>
> Your solution works fine, I would appreciated your prompt reply.
> Thanks a lot
>
>
> -------- Original Message --------
> Subject: Re:[SQL] Subquery problems
> From: Masaru Sugawara <rk73(at)ghost(dot)plala(dot)or(dot)jp>
> To: Ranieri Mazili <ranieri(dot)oliveira(at)terra(dot)com(dot)br>
> Date: 21/6/2007 13:25
> > On Tue, 19 Jun 2007 09:17:22 -0300
> > Ranieri Mazili <ranieri(dot)oliveira(at)terra(dot)com(dot)br> wrote:
> >
> > Hi,
> >
> > This reply is not accurate, but I think there are helpful hints.
> >
> > --
> > Masaru Sugawara
> >
> >
> >
> >
> > select
> > C.id_production_area,
> > B.id_machine_type,
> > A.h_month as month,
> > max(A.n) as div_mes,
> > cast((sum(A.qty_employees_total)
> > -(sum(A.qty_absence)
> > -sum(A.qty_vacation)
> > -sum(A.qty_diseased)
> > -sum(A.qty_indirect)
> > -sum(A.qty_transferred))
> > +sum(A.qty_received))/max(A.n) as integer)
> > from (select *
> > from
> > (select *, extract(month from h1.head_count_date) as h_month
> > from head_count as h1
> > where extract(year from h1.head_count_date)
> > = extract(year from current_date)
> > ) as h2,
> > (select extract(month from production_date) as p_month,
> > count(distinct p1.production_date) as n
> > from production as p1
> > where extract(year from production_date)
> > = extract(year from current_date)
> > group by extract(month from production_date)
> > ) as p2
> > where h2.h_month = p2.p_month
> > ) as A, machine B, machine_type C
> > where A.id_machine = B.id_machine
> > AND B.id_machine_type = C.id_machine_type
> > group by C.id_production_area, B.id_machine_type, A.h_month
> > order by C.id_production_area, A.h_month, A.h_month DESC;
> >
> >
> >
> >
> >
> >