-------- Original Message --------
Subject: Re:[GENERAL] Subquery problems
From: Merlin Moncure <mmoncure@xxxxxxxxx>
To: Ranieri Mazili <ranieri.oliveira@xxxxxxxxxxxx>
Date: 19/6/2007 10:40
On 6/19/07, Ranieri Mazili <ranieri.oliveira@xxxxxxxxxxxx> 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
