Thread: Subquery problems

Hello,

I'm having another "problem", I have a function that declares 12 variable, one per month and each them execute a select like bellow:
DIV_MES01 := (select count(distinct production_date) from production where extract(month from production_date) = '01' and extract(year from production_date) = EXTRACT(YEAR FROM current_date));

Then, I need to check if the variable is equal 0:
IF DIV_MES01 = 0 THEN
   DIV_MES01 := 1;
END IF;

Finally,  I perform the following query:

SELECT    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))/DIV_MES01) AS integer),
          C.id_production_area,
          cast('01' as text) AS mes
FROM      head_count A, machine B, machine_type C
WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM current_date)
          AND EXTRACT(MONTH FROM head_count_date) = '01'
          AND 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

Doing it, I need to perform 12 querys united by "UNION", what I want to do is unify it in only one query, I tryed with the query bellow:

SELECT     date_trunc('month', A.head_count_date)::date as head_date,
           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))/(select   count(distinct production_date)
                                      from     production
                                      where    extract(month from production_date) = EXTRACT(MONTH FROM date_trunc('month', A.head_count_date)::date)
                                               and extract(year from production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
           C.id_production_area
FROM     head_count A, machine B, machine_type C
WHERE    date_trunc('month', A.head_count_date)::date BETWEEN
         date_trunc('month', current_date - (EXTRACT(MONTH FROM current_date)-1) * interval '1 month')::date
         AND date_trunc('month', current_date)::date
         AND 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, head_count_date,head_date
ORDER BY id_production_area, head_count_date,head_date DESC

But the results aren't what I want.
What I trying to do is possible?

I appreciate any help.
Thanks

On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
>
>  Hello,
>
>  I'm having another "problem", I have a function that declares 12 variable,
> one per month and each them execute a select like bellow:
>  DIV_MES01 := (select count(distinct production_date) from production where
> extract(month from production_date) = '01' and extract(year from
> production_date) = EXTRACT(YEAR FROM current_date));
>
>  Then, I need to check if the variable is equal 0:
>  IF DIV_MES01 = 0 THEN
>     DIV_MES01 := 1;
>  END IF;
>
>  Finally,  I perform the following query:
>
>  SELECT    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))/DIV_MES01) AS integer),
>            C.id_production_area,
>            cast('01' as text) AS mes
>  FROM      head_count A, machine B, machine_type C
>  WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
> current_date)
>            AND EXTRACT(MONTH FROM head_count_date) = '01'
>            AND 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
>
>  Doing it, I need to perform 12 querys united by "UNION", what I want to do
> is unify it in only one query, I tryed with the query bellow:
>
>  SELECT     date_trunc('month', A.head_count_date)::date as head_date,
>             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))/(select   count(distinct production_date)
>                                        from     production
>                                        where    extract(month from
> production_date) = EXTRACT(MONTH FROM date_trunc('month',
> A.head_count_date)::date)
>                                                 and extract(year from
> production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
>             C.id_production_area
>  FROM     head_count A, machine B, machine_type C
>  WHERE    date_trunc('month', A.head_count_date)::date BETWEEN
>           date_trunc('month', current_date - (EXTRACT(MONTH FROM
> current_date)-1) * interval '1 month')::date
>           AND date_trunc('month', current_date)::date
>           AND 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, head_count_date,head_date
>  ORDER BY id_production_area, head_count_date,head_date DESC
>
>  But the results aren't what I want.
>  What I trying to do is possible?
>
>  I appreciate any help.
>  Thanks

sure!.

  SELECT    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))/DIV_MES01) AS integer),
            C.id_production_area,
            cast(DIV_MES01 as text) AS mes
  FROM      head_count A, machine B, machine_type C,
(
  select case when ct = 0 then 1 else ct end as DIV_MES01 from
  (
    select count(distinct production_date) as ctfrom production where
   extract(year from production_date) = EXTRACT(YEAR FROM current_date)
  ) q
) D
  WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
 current_date)
            AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
            AND 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;

ok, I didn't syntax check this monster, but it should give you a
start...the trick is to use an 'inline view' to expand your variable
list into a set.

merlin

-------- Original Message  --------
Subject: Re:[GENERAL] Subquery problems
From: Merlin Moncure <mmoncure@gmail.com>
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Date: 19/6/2007 10:40
> On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
>>
>>  Hello,
>>
>>  I'm having another "problem", I have a function that declares 12
>> variable,
>> one per month and each them execute a select like bellow:
>>  DIV_MES01 := (select count(distinct production_date) from production
>> where
>> extract(month from production_date) = '01' and extract(year from
>> production_date) = EXTRACT(YEAR FROM current_date));
>>
>>  Then, I need to check if the variable is equal 0:
>>  IF DIV_MES01 = 0 THEN
>>     DIV_MES01 := 1;
>>  END IF;
>>
>>  Finally,  I perform the following query:
>>
>>  SELECT    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))/DIV_MES01) AS integer),
>>            C.id_production_area,
>>            cast('01' as text) AS mes
>>  FROM      head_count A, machine B, machine_type C
>>  WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
>> current_date)
>>            AND EXTRACT(MONTH FROM head_count_date) = '01'
>>            AND 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
>>
>>  Doing it, I need to perform 12 querys united by "UNION", what I want
>> to do
>> is unify it in only one query, I tryed with the query bellow:
>>
>>  SELECT     date_trunc('month', A.head_count_date)::date as head_date,
>>             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))/(select   count(distinct
>> production_date)
>>                                        from     production
>>                                        where    extract(month from
>> production_date) = EXTRACT(MONTH FROM date_trunc('month',
>> A.head_count_date)::date)
>>                                                 and extract(year from
>> production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
>>             C.id_production_area
>>  FROM     head_count A, machine B, machine_type C
>>  WHERE    date_trunc('month', A.head_count_date)::date BETWEEN
>>           date_trunc('month', current_date - (EXTRACT(MONTH FROM
>> current_date)-1) * interval '1 month')::date
>>           AND date_trunc('month', current_date)::date
>>           AND 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,
>> head_count_date,head_date
>>  ORDER BY id_production_area, head_count_date,head_date DESC
>>
>>  But the results aren't what I want.
>>  What I trying to do is possible?
>>
>>  I appreciate any help.
>>  Thanks
>
> sure!.
>
>  SELECT    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))/DIV_MES01) AS integer),
>            C.id_production_area,
>            cast(DIV_MES01 as text) AS mes
>  FROM      head_count A, machine B, machine_type C,
> (
>  select case when ct = 0 then 1 else ct end as DIV_MES01 from
>  (
>    select count(distinct production_date) as ctfrom production where
>   extract(year from production_date) = EXTRACT(YEAR FROM current_date)
>  ) q
> ) D
>  WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
> current_date)
>            AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
>            AND 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;
>
> ok, I didn't syntax check this monster, but it should give you a
> start...the trick is to use an 'inline view' to expand your variable
> list into a set.
>
> merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
On this way didn't work, I wanna do only one query to return the data of
entire year, not only one month, but thanks for try.
If someone have an idea of how do it, please, help :D

Thanks

On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
> > sure!.
> >
> >  SELECT    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))/DIV_MES01) AS integer),
> >            C.id_production_area,
> >            cast(DIV_MES01 as text) AS mes
> >  FROM      head_count A, machine B, machine_type C,
> > (
> >  select case when ct = 0 then 1 else ct end as DIV_MES01 from
> >  (
> >    select count(distinct production_date) as ctfrom production where
> >   extract(year from production_date) = EXTRACT(YEAR FROM current_date)
> >  ) q
> > ) D
> >  WHERE     EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
> > current_date)
> >            AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
> >            AND 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;
> >
> > ok, I didn't syntax check this monster, but it should give you a
> > start...the trick is to use an 'inline view' to expand your variable
> > list into a set.
> >
> On this way didn't work, I wanna do only one query to return the data of
> entire year, not only one month, but thanks for try.
> If someone have an idea of how do it, please, help :D
>

the inlne view (table 'D') breaks down the year month by month.  maybe
you have to add DIV_MES01 to the group  by?

merlin