Quantcast

Question on query failing

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Question on query failing

Bayless Kirtley
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Question on query failing

Fred Toussi-2
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

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

_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Question on query failing

Bayless Kirtley
In reply to this post by Bayless Kirtley
Thanks Fred. It sounds like I need a serious SQL lesson. I apologize for bothering you with basic questions like this.
 
Bayless
 
 
Sent: Thursday, November 19, 2015 4:29 PM
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


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


_______________________________________________
Hsqldb-user mailing list
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...