Re: [GENERAL] Subquery problems - Mailing list pgsql-sql

-------- 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