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