I have a view:
DROP VIEW InvoiceSum;
CREATE VIEW InvoiceSum as SELECT i.InvoiceID, i.DatePrinted, il.MemberID,
sum((il.UnitPrice * il.Quantity) + il.ShippingHandling) AS AmountOfInvoice FROM Invoice i, InvoiceLines il WHERE
i.InvoiceID= il.InvoiceID group by i.InvoiceID, i.DatePrinted, il.MemberID;
The following works great:
select * from invoicesum where memberid = 685;
The following fails:
select MemberID, sum(AmountOfInvoice) as InvAmt from InvoiceSum where memberid = 685;
ERROR: Illegal use of aggregates or non-group column in target list
The following also fails:
select MemberID, sum(AmountOfInvoice) as InvAmt from InvoiceSum where memberid = 685 group by memberid;
ERROR: ExecAgg: Bad Agg->Target for Agg 0I get this error with or without the where clause.
I have many complex queries like this that I would like (need) to port to
PostgreSQL. As a result, this limitation will be difficult for me to work
around. I would be willing to explore fixing this for 6.6 if someone would
be willing to point me in the right direction and tell me where to start
looking in the code and possibly what to look for. The more information the
better.
I would also like to make views updateable without having to add rules.
The other limitation that is presenting some challenges is the lack of outer
joins. Is any portion of outer join supported? Could I find out when outer
join support is planned for implementation?
Thanks, Michael