db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Alias referencing from the HAVING clause
Date Mon, 28 Aug 2006 19:35:51 GMT
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




Mime
View raw message