From: | Michael J Davis <michael(dot)j(dot)davis(at)tvguide(dot)com> |
---|---|
To: | hackers(at)postgresql(dot)org |
Subject: | A select with aggretion is failing, still subtle problems with ag gregation |
Date: | 1999-05-01 01:20:18 |
Message-ID: | 93C04F1F5173D211A27900105AA8FCFC14545B@lambic.prevuenet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
The following select fails:
> select invoiceid + 3 as type, memberid, 1, max(TotShippingHandling)
> from InvoiceLineDetails
> where TotShippingHandling <> 0
> group by type, memberid limit 10;
ERROR: replace_agg_clause: variable not in target list
The following select works (the + 3 has been eliminated):
> select invoiceid as type, memberid, 1, max(TotShippingHandling)
> from InvoiceLineDetails
> where TotShippingHandling <> 0
> group by type, memberid limit 10;
type|memberid|?column?| max
-----+--------+--------+-----
15499| 1626| 1| 6.00
15524| 138| 1| 3.00
15647| 1083| 1|20.00
15653| 1230| 1| 4.00
15659| 1600| 1| 3.00
15671| 1276| 1| 3.50
15672| 1494| 1| 3.00
15673| 1653| 1| 4.50
15674| 1624| 1| 6.00
15675| 1406| 1| 7.00
(10 rows)
Here is a description of the view InvoiceLineDetails:
> \d InvoiceLineDetails
View = invoicelinedetails
Query = Not a view
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| invoicelinesid | int4 |
4 |
| invoiceid | int4 |
4 |
| dateprinted | datetime |
8 |
| ordersid | int4 |
4 |
| ordertypeid | int4 |
4 |
| totshippinghandling | numeric |
var |
| shippeddate | datetime |
8 |
| memberid | int4 |
4 |
| gift | numeric |
var |
| shippinghandling | numeric |
var |
| unitcost | numeric |
var |
| unitprice | numeric |
var |
| quantity | int4 |
4 |
| invamount | numeric |
var |
| inventoryid | int4 |
4 |
| inventoryname | varchar() |
0 |
| inventorytypeid | int4 |
4 |
| inventorytypename | varchar() |
32 |
| categoriesid | int4 |
4 |
| tapenum | int4 |
4 |
+----------------------------------+----------------------------------+-----
--+
From | Date | Subject | |
---|---|---|---|
Next Message | Vadim Mikheev | 1999-05-01 08:23:22 | locking... |
Previous Message | Bruce Momjian | 1999-04-30 20:56:59 | Re: [HACKERS] Optimizer fed bad data about some system-table indexes |