Re: Subquery problems

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