The following bug has been logged online:
Bug reference: 4386
Logged by: David Chen
Email address: nychendavid@yahoo.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