Re: Alias referencing from the HAVING clause

[Christine Johnson]

Sorry about the reply-free post a bit earlier.   I thought adding a GROUP BY clause to the SQL might be useful, but tinkering shows me it's not.   Regards, Chris Johnson ----- Original Message ----- From: Robert Enyedi To: Derby Discussion Sent: Monday, August 28, 2006 8:15 AM Subject: Alias referencing from the HAVING clause Hi, I'm trying to write a simple query in Derby which looks the following way: SELECT SUM(PROD.visible) AS visible_val FROM products AS PROD HAVING visible_val > 0 However I receive an error from Derby: ERROR 42X04: Column 'VISIBLE_VAL' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'VISIBLE_VAL' is not a column in the target table. Rewriting the query this way produces the desired result: SELECT SUM(PROD.visible) FROM products AS PROD HAVING SUM(PROD.visible) > 0 but I find this variant to be hard to read and unmaintainable in a large query. Isn't it possible to reference an alias from inside the HAVING clause? The documentation does not touch this issue. Or is there a more maintainable way to rewrite the erroneous query? Regards, Robert