Re: Question on query failing

Thanks Fred. It sounds like I need a serious SQL lesson. I apologize for bothering you with basic questions like this.

Bayless


From: Fred Toussi 
Sent: Thursday, November 19, 2015 4:29 PM
To: User discussions about HyperSQL Database Engine. 
Subject: Re: [Hsqldb-user] Question on query failing

You cannot reference a label such as TheYear in the where clause. The actual Year function can be used.

As your query is grouped, use HAVING after the GROUP BY. You cannot use WHERE with an aggregate value. You can use the Year function in either the WHERE or HAVING clause (if you use it in the WHERE clause, there is no point grouping by TheYear as there will be only a single year group).

WHERE Year(DONATIONS.DONATIONDATE) = 1024
GROUP BY Year(DONATIONS.DONATIONDATE) , DONOR.COMPATY, DONATIONS.DONATIONDATE 
HAVING Sum(DONTATIONS.TOTAL) >=100
Fred

On Thu, Nov 19, 2015, at 20:09, Bayless Kirtley wrote:
  The following query fails.

  Select DONORS.COMPANY As Company, Sum(DONATIONS.FOOD) As Food, Sum(DONATIONS.CHECKS) As Check, Sum(DONATIONS.CASH) As Cash,
  Sum(DONATIONS.MISC) As Misc, Sum(DONATIONS.LABOR) As Labor, Sum(DONATIONS.TOTALS) As Totals, Year(DONATIONS.DONATIONDATE) As TheYear 
  From DONORS Left Join DONATIONS On DONORS.ID =  DONATIONS.DONORID
  Where Totals >= 100.00 And TheYear = 2014
  Group By TheYear, DONORS.COMPANY, DONATIONS.DONATIONDATE
  Order By DONORS.COMPANY

  The message is user lacks privilege or object not found: THEYEAR / Error Code: –5501 / State: 42501

  The offending part seems to be TheYear in the Where clause. If I remove that, it works but not what I need, of course. I also tried substituting
  Year(donations.donationdate) with the same result. It seems that I cannot use a function or the result of a function in the Where clause. Is
  this correct or am I committing some other error? All the columns do exist. I am using HSQL 2.3.3.

  Thanks,
  Bayless

  ------------------------------------------------------------------------------
  _______________________________________________
  Hsqldb-user mailing list
  [email protected]
  https://lists.sourceforge.net/lists/listinfo/hsqldb-user


--------------------------------------------------------------------------------
------------------------------------------------------------------------------



--------------------------------------------------------------------------------
_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user



--------------------------------------------------------------------------------

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7227 / Virus Database: 4460/11029 - Release Date: 11/19/15

------------------------------------------------------------------------------

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user