----- Original Message -----
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
|
- Re: Alias referencing from the HAVING clause Christine Johnson
-
