Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 90B169FBB81; Tue, 19 Jun 2007 09:17:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 93783-05; Tue, 19 Jun 2007 09:17:33 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sr05-09.mta.terra.com.br (sr05-09.mta.terra.com.br [200.154.152.68]) by postgresql.org (Postfix) with ESMTP id 7E1679FB755; Tue, 19 Jun 2007 09:17:33 -0300 (ADT) Received: from tarmula.hst.terra.com.br (tarmula.hst.terra.com.br [200.176.10.4]) by buraja.hst.terra.com.br (Postfix) with ESMTP id 5C0DC4914177; Tue, 19 Jun 2007 09:17:31 -0300 (BRT) X-Terra-Karma: -2% X-Terra-Hash: 19e32b25dfd6ed4292f302bd1e78b31b Received-SPF: pass (tarmula.hst.terra.com.br: domain of terra.com.br designates 200.176.10.4 as permitted sender) client-ip=200.176.10.4; envelope-from=ranieri.oliveira@terra.com.br; helo=[172.16.2.72]; Received: from [172.16.2.72] (200-162-219-207.corp.ajato.com.br [200.162.219.207]) (authenticated user ranieri.oliveira) by tarmula.hst.terra.com.br (Postfix) with ESMTP id 1A3E458164; Tue, 19 Jun 2007 09:17:31 -0300 (BRT) Message-ID: <4677C952.9060902@terra.com.br> Date: Tue, 19 Jun 2007 09:17:22 -0300 From: Ranieri Mazili User-Agent: Thunderbird 2.0.0.4 (Windows/20070604) MIME-Version: 1.0 To: pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Subquery problems Content-Type: multipart/alternative; boundary="------------050808020509000400090201" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.853 tagged_above=0 required=5 tests=AWL=-0.191, BAYES_50=0.001, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, HTML_50_60=0.134, HTML_MESSAGE=0.001 X-Spam-Level: * X-Archive-Number: 200706/1142 X-Sequence-Number: 115965 This is a multi-part message in MIME format. --------------050808020509000400090201 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------050808020509000400090201 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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

--------------050808020509000400090201--