From: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Subquery problems
Date: Tue, 19 Jun 2007 11:59:05 -0300
Message-ID: <4677EF39.6050904@terra.com.br> (raw)
In-Reply-To: <b42b73150706190640m7eb90d6bg1468765876b13665@mail.gmail.com>
References: <4677C952.9060902@terra.com.br>
<b42b73150706190640m7eb90d6bg1468765876b13665@mail.gmail.com>
-------- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ranieri.oliveira@terra.com.br, pgsql-general@postgresql.org
Subject: Re: Subquery problems
In-Reply-To: <4677EF39.6050904@terra.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox