Re: Subquery problems - Mailing list pgsql-general

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