Skip site navigation (1) Skip section navigation (2)

BUG #4387: UNION in Crosstab - missing rows

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 (view raw or flat)
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

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2008-08-29 13:10:10
Subject: Re: libpq does not manage SSL callbacks properly when otherlibraries are involved.
Previous:From: David ChenDate: 2008-08-29 03:49:51
Subject: BUG #4386: UNION in Crosstab - missing rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group