db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Christine Johnson" <cjohn...@willowtech.com>
Subject Re: Alias referencing from the HAVING clause
Date Mon, 28 Aug 2006 21:11:29 GMT
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

Chris Johnson
  ----- Original Message ----- 
  From: Robert Enyedi 
  To: Derby Discussion 
  Sent: Monday, August 28, 2006 8:15 AM
  Subject: Alias referencing from the HAVING clause


  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?


View raw message