Views, aggregations, and errors
От | Michael Davis |
---|---|
Тема | Views, aggregations, and errors |
Дата | |
Msg-id | 93C04F1F5173D211A27900105AA8FCFC1452F6@lambic.prevuenet.com обсуждение исходный текст |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: