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 0
	I 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


Tue, 30 Mar 1999 10:11:57 -0600
Michael Davis <michael.davis@prevuenet.com>
Views, aggregations, and errors