Обсуждение: Views, aggregations, and errors

Поиск
Список
Период
Сортировка

Views, aggregations, and errors

От
Michael Davis
Дата:
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