db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Enyedi <robert.eny...@intland.com>
Subject Alias referencing from the HAVING clause
Date Mon, 28 Aug 2006 15:15:11 GMT
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

Mime
View raw message