Chris,
Thanks anyway!
Regards,
Robert
Christine Johnson wrote:
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 <mailto:[EMAIL PROTECTED]>
*To:* Derby Discussion <mailto:[email protected]>
*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
