Re: Subquery problems

On 6/19/07, Ranieri Mazili <ranieri(dot)oliveira(at)terra(dot)com(dot)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