From: | "David Chen" <nychendavid(at)yahoo(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4387: UNION in Crosstab - missing rows |
Date: | 2008-08-29 06:11:46 |
Message-ID: | 200808290611.m7T6BkVj084498@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4387
Logged by: David Chen
Email address: nychendavid(at)yahoo(dot)com
PostgreSQL version: 8.2.9
Operating system: Windows 2003
Description: UNION in Crosstab - missing rows
Details:
Hi,
I wish to report a bug in Crosstab func as
I am using the crosstab tablefunc to create pivot table.
All required rows are found in select union select query.
eg.
SELECT cu.customerid, cu.name, '10'::bpchar AS periodcode,
sum(iv.invoice_balance) as amt
FROM rapiderp.invoices iv
LEFT JOIN rapiderp.customers cu ON cu.customerid=iv.customerid
where iv.invoice_balance>0 and iv.period_id<151-7 and cu.requirestatement
is true
group by cu.customerid, cu.name,iv.invoice_balance, iv.period_id
UNION
SELECT ar.customerid, cu.name, '10'::bpchar AS periodcode,
sum(nn.notes_balance) AS amt
FROM rapiderp.agenttransactions ar
LEFT JOIN rapiderp.notes nn ON nn.notes_id = ar.sourceid
LEFT JOIN rapiderp.customers cu on ar.customerid=cu.customerid
WHERE ar.sourcetype = 'DN'::bpchar AND ar.period_id = (151 - 7) AND
nn.notes_balance > 0::numeric and cu.requirestatement is true
GROUP BY ar.customerid, cu.name
output:
2573;"Chin, Lau, Wong & Foo";"10";145.53
2573;"Chin, Lau, Wong & Foo";"10";166.32
2573;"Chin, Lau, Wong & Foo";"10";374.22
2694;"Insight Works Sdn Bhd";"10";430.92
2765;"Lucky Advertising Agency";"10";47.12
2765;"Lucky Advertising Agency";"10";94.25
But in the crosstab:
2573;"Chin, Lau, Wong & Foo";1060.29;1060.29;;;;;;374.22;;374.22;;;
The 145.53 and 166.32 were not added to 374.22 for 2573.
Please assist ..
Rgds
David
The following bug has been logged online:
Bug reference: 4386
Logged by: David Chen
Email address: nychendavid(at)yahoo(dot)com
PostgreSQL version: 8.2.9
Operating system: Windows 2003
Description: UNION in Crosstab - missing rows
Details:
Hi,
I wish to report a bug in Crosstab func as
I am using the crosstab tablefunc to create pivot table.
All required rows are found in select union select query.
eg.
SELECT cu.customerid, cu.name, '10'::bpchar AS periodcode,
sum(iv.invoice_balance) as amt
FROM rapiderp.invoices iv
LEFT JOIN rapiderp.customers cu ON cu.customerid=iv.customerid
where iv.invoice_balance>0 and iv.period_id<151-7 and cu.requirestatement
is true
group by cu.customerid, cu.name,iv.invoice_balance, iv.period_id
UNION
SELECT ar.customerid, cu.name, '10'::bpchar AS periodcode,
sum(nn.notes_balance) AS amt
FROM rapiderp.agenttransactions ar
LEFT JOIN rapiderp.notes nn ON nn.notes_id = ar.sourceid
LEFT JOIN rapiderp.customers cu on ar.customerid=cu.customerid
WHERE ar.sourcetype = 'DN'::bpchar AND ar.period_id = (151 - 7) AND
nn.notes_balance > 0::numeric and cu.requirestatement is true
GROUP BY ar.customerid, cu.name
output:
2573;"Chin, Lau, Wong & Foo";"10";145.53
2573;"Chin, Lau, Wong & Foo";"10";166.32
2573;"Chin, Lau, Wong & Foo";"10";374.22
2694;"Insight Works Sdn Bhd";"10";430.92
2765;"Lucky Advertising Agency";"10";47.12
2765;"Lucky Advertising Agency";"10";94.25
But in the crosstab:
2573;"Chin, Lau, Wong & Foo";1060.29;1060.29;;;;;;374.22;;374.22;;;
The 145.53 and 166.32 were not added to 374.22 for 2573.
Please assist ..
Rgds
David
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2008-08-29 13:10:10 | Re: libpq does not manage SSL callbacks properly when other libraries are involved. |
Previous Message | David Chen | 2008-08-29 03:49:51 | BUG #4386: UNION in Crosstab - missing rows |