On Wed, Oct 1, 2008 at 3:07 AM, Decibel! <[EMAIL PROTECTED]> wrote: > From -HEAD: > > ERROR: aggregates not allowed in WHERE clause > STATEMENT: SELECT * > FROM loans l > WHERE id IN ( SELECT max(l.id) > FROM loans > JOIN customers c ON c.id = > l.customer_id > JOIN people p ON p.id = c.person_id > WHERE p.first_name = 'Test person' > GROUP BY l.loan_type_cd > ) > ; > > The real issue is this: > > ERROR: missing FROM-clause entry for table "l" at character 132 > STATEMENT: SELECT max(l.id) > FROM loans > JOIN customers c ON c.id = > l.customer_id > JOIN people p ON p.id = > c.person_id > WHERE p.first_name = 'Test > person' > GROUP BY l.loan_type_cd; > > And if I change the FROM loans to be FROM loans l, the original select does > work fine. > > Let me know if I need to create a full test case for this... >
No, the real issue is that you are referencing the outer table's column's max() in the inner query (correlated sub-query). The table in the outer query is aliased 'l' and the sub-query is trying to aggregate that, which is in the outer query's WHERE clause; and that is not allowed. Renaming the outer query's alias to something other than 'l' would show you the real error (which again would be 'missing FROM-clause entry). Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
