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 Re: Alias referencing from the HAVING clause
Date Tue, 29 Aug 2006 07:27:21 GMT
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:robert.enyedi@intland.com>
>     *To:* Derby Discussion <mailto:derby-user@db.apache.org>
>     *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
>


Mime
View raw message