db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Manish Khettry" <manish.khet...@gmail.com>
Subject Re: Alias referencing from the HAVING clause
Date Wed, 30 Aug 2006 02:08:28 GMT
Derby-883 allows expressions in the group by list whereas this has to do
with how Derby binds expressions in group by/having queries. Another similar
issue is Derby-1624.

Manish

On 8/28/06, Stanley Bradbury <Stan.Bradbury@gmail.com> wrote:
>
> 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