Robert Enyedi wrote:
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
It looks to me like the fix for DERBY-883 might address this problem.
You can download the beta version of 10.2 and test it, if you do so
please post your results to this thread as feedback on the 10.2 feature.
The issue can be viewed at: http://issues.apache.org/jira/browse/DERBY-883
HTH
