Re: 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