Click here to Skip to main content
15,919,245 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

The query is this
SQL
DECLARE @ischecked BIT =1

SELECT 
ticketmaster.id as ticketid,
ticketmaster.ticketnumber as ticketnumber,
ticketdetail.subject as subject,
ticketmaster.priority as priority,
ticketmaster.status as status,
ticketdetail.createddate as lastupdated,
Distributor.Name as Company
FROM ticketmaster 
LEFT JOIN ticketdetail ON ticketmaster.ID = ticketdetail.ticketmasterid AND MAX(ticketdetail.createddate)=1	LEFT JOIN Distributor ON  ticketmaster.distributorid=Distributor.ID
WHERE ticketmaster.status IN (0,1) AND ((ticketmaster.distributorid=0 AND 
ticketmaster.issamelevel=1) OR (@ischecked =1 AND (ticketmaster.distributorid<>0 AND
ticketmaster.issamelevel=0 AND resellerid=0)))
GROUP BY ticketdetail.createddate

select * from ticketmaster
Posted
Updated 6-Jun-12 20:56pm
v2

Error should be because of AND MAX(ticketdetail.createddate)=1
Try:
SQL
DECLARE @ischecked BIT =1
 
SELECT 
ticketmaster.id as ticketid,
ticketmaster.ticketnumber as ticketnumber,
ticketdetail.subject as subject,
ticketmaster.priority as priority,
ticketmaster.status as status,
ticketdetail.createddate as lastupdated,
Distributor.Name as Company
FROM ticketmaster 
LEFT JOIN ticketdetail ON ticketmaster.ID = ticketdetail.ticketmasterid LEFT JOIN Distributor ON  ticketmaster.distributorid=Distributor.ID
WHERE ticketmaster.status IN (0,1) AND ((ticketmaster.distributorid=0 AND 
ticketmaster.issamelevel=1) OR (@ischecked =1 AND (ticketmaster.distributorid<>0 AND
ticketmaster.issamelevel=0 AND resellerid=0)))
GROUP BY ticketdetail.createddate
HAVING MAX(ticketdetail.createddate)=1 

select * from ticketmaster


Details here:
http://www.sql-server-performance.com/2007/aggregate-cannot-appear-in-on-clause/[^]
http://exacthelp.blogspot.in/2012/04/aggregate-cannot-appear-in-on-clause.html[^]
 
Share this answer
 
v2
Comments
VJ Reddy 7-Jun-12 3:22am    
Good answer and references. 5!
Sandeep Mewara 7-Jun-12 16:10pm    
Thanks.
Maciej Los 7-Jun-12 3:26am    
Good answer, my 5!
Sandeep, please correct answer in this line: HAVINGA MAX(ticketdetail.createddate)=1
Sandeep Mewara 7-Jun-12 16:11pm    
Thanks losmac. Corrected.
Maciej Los 7-Jun-12 16:16pm    
;)
Here is updated Query :

SQL
DECLARE @ischecked BIT =1
 
SELECT 
ticketmaster.id as ticketid,
ticketmaster.ticketnumber as ticketnumber,
ticketdetail.subject as subject,
ticketmaster.priority as priority,
ticketmaster.status as status,
ticketdetail.createddate as lastupdated,
Distributor.Name as Company
FROM ticketmaster 
LEFT JOIN ticketdetail ON ticketmaster.ID = ticketdetail.ticketmasterid 
LEFT JOIN Distributor ON  ticketmaster.distributorid=Distributor.ID
WHERE ticketmaster.status IN (0,1) AND ((ticketmaster.distributorid=0 AND 
ticketmaster.issamelevel=1) OR (@ischecked =1 AND (ticketmaster.distributorid<>0 AND
ticketmaster.issamelevel=0 AND resellerid=0)))
GROUP BY ticketmaster.id as ticketid,
ticketmaster.ticketnumber as ticketnumber,
ticketdetail.subject as subject,
ticketmaster.priority as priority,
ticketmaster.status as status,
ticketdetail.createddate as lastupdated,
Distributor.Name as Company
HAVING MAX(ticketdetail.createddate)=1

 
select * from ticketmaster




Hope this helps , If yes then plz accept and vote the answer. Any queries / questions on this are always welcome.

Thanks & Regards
RDBurmon.Sr.Software Engineer
 
Share this answer
 
Comments
Maciej Los 7-Jun-12 4:18am    
The comment from OP:
i put the having condtion as another select inside where clause.. Thanks a lot guys

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900