An Aggregate may not appear in the Where clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

An Aggregate may not appear in the Where clause

Author

Topic

Damian39
Starting Member

33 Posts

Posted - 2014-09-26 : 12:48:41

Hello all,
I've written a query which was working fine prior to me trying to add a sub-query. I'm trying to make sure I am getting the max disconnect date for each account that has been issued a refund. When I attempt to run the modified query, I receive the following error:
Msg 147, Level 15, State 1, Line 43
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Here's the whole query:



IF OBJECT_ID('TEMPDB..#TMP1A') IS NOT NULL DROP TABLE #TMP1A

SELECT 
BC.ACCTCORP,
BD.HOUSE,
BD.CUST,
BC.BATCH,
BC.BADATE,
BC.BATYPE,
BD.AMOUNT,
BD.ADJRSN,
BC.OPR AS OPID,
BC.CTLGRP,
BD.POSTFLAG

INTO #TMP1A

FROM INFODDPFLORIDA.GGS.IDST_BATCH_CATALOG BC WITH(NOLOCK) 
INNER JOIN INFODDPFLORIDA.GGS.IDST_BATCH_DETAIL BD WITH(NOLOCK)

ON		BC.ACCTCORP=BD.ACCTCORP 
AND		BC.BATCH=BD.BATCH 
AND		BC.BADATE=BD.BADATE

WHERE	BC.CTLGRP = 'REFND' 
AND	BC.BADATE BETWEEN '2013-10-01 00:00:00' AND getdate()


SELECT DISTINCT A.ACCTCORP,
			    RIGHT('00000' + CAST(A.ACCTCORP AS VARCHAR),5) +
			    RIGHT('000000' + CAST(A.HOUSE AS VARCHAR),6) +
			    RIGHT('00' + CAST(A.CUST AS VARCHAR),2) AS 'ACCOUNT_NUMBER',
			    C.FNAME + ' ' + C.LNAME AS FULL_NAME,
			    C.BDATE,
                A.BADATE AS BATCH_DATE,
			    A.AMOUNT AS REF_AMOUNT,
			    A.OPID,
               (C.CUR+C.C30+C.C60+C.C90+C.C120) AS TotalBalance,
                A.ADJRSN,
                --CO.BNUMB AS 'EQUIPMENT',
                BX.BNUMB AS EQUIP_SERIAL_NO,
                BX.PREVHOUSE,
                BX.LASTDATE,
                A.BATCH,
                A.CTLGRP,
                A.POSTFLAG
            

FROM  #TMP1A A (NOLOCK), INFODDPFlorida.ggs.IDST_CUSTOMER C (NOLOCK), INFODDPFlorida.ggs.IDST_CUST_OUTLET CO (NOLOCK), INFODDPFlorida.ggs.IDST_BOX_INVENTORY BX (NOLOCK)



WHERE		A.ACCTCORP	=	C.ACCTCORP 
AND		A.HOUSE		=	C.HOUSE 
AND		A.CUST		=	C.CUST
	
AND		C.ACCTCORP	=	CO.ACCTCORP 
AND		C.HOUSE		=	CO.HOUSE 
AND		C.CUST		=	CO.CUST
			
AND		CO.ACCTCORP	=	BX.ACCTCORP 
AND		CO.HOUSE	=	BX.HOUSE 
AND		CO.CUST		=	BX.CUST
	
AND C.BDATE = (SELECT MAX(C.BDATE) AS DISCO_DTE FROM INFODDPFlorida.ggs.IDST_CUSTOMER AS B (NOLOCK)
			   WHERE C.ACCTCORP = B.ACCTCORP
			   AND	 C.HOUSE = B.HOUSE
			   AND	 C.CUST = B.CUST
			   HAVING C.BDATE < A.BADATE)

			   
AND C.STAT = '6'
AND A.BADATE > C.BDATE

	  
GROUP BY A.ACCTCORP,
	 A.HOUSE,
	 A.CUST,
	 C.FNAME,
	 C.LNAME,
	 C.BDATE,
         A.BADATE,
	 A.AMOUNT,
	 A.OPID,
         C.CUR,
         C.C30,
         C.C60,
         C.C90,
         C.C120,
         A.ADJRSN,
         BX.BNUMB,
         BX.PREVHOUSE,
         BX.LASTDATE,
         A.BATCH,
         A.CTLGRP,
         A.POSTFLAG

Any and all help is greatly appreciated! Thanks

Damian

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-26 : 13:38:03

well the message means what it says, the aggregation 'MAX(C.BDATE)' is in a subquery contained in a where clause, not a having clause or select list. i'm not sure what you're trying to achieve, but i got a toy version of this idea to work:


declare @t1 table (a int, b int)
declare @t2 table (a int, b int)
insert into @t1(a,b) values
	(1,2),
	(2,3)
insert into @t2(a,b) values
	(1,2),
	(2,3)
	
select t1.b from @t1 t1
group by t1.b,t1.a
having a = (select max(t2.a) from @t2 t2 where t1.a = t2.a)

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-27 : 08:09:33

quote:

Originally posted by Damian39

AND C.BDATE = (SELECT MAX(C.BDATE) AS DISCO_DTE FROM INFODDPFlorida.ggs.IDST_CUSTOMER AS B (NOLOCK)
			   WHERE C.ACCTCORP = B.ACCTCORP
			   AND	 C.HOUSE = B.HOUSE
			   AND	 C.CUST = B.CUST
			   HAVING C.BDATE < A.BADATE)

The above section makes very little sense to me.

In the having section, the expression (HAVING C.BDATE < A.BADATE) references tables outside this subquery. If your expression doesn't involve tables in you subquery, why have the expression in the subquery? Besides, the same expression is outside (2 lines down from the subquery) - at least it is the same logic (AND A.BADATE > C.BDATE).

Also the aggregate is used on field outside this subquery (MAX(C.BDATE)).

Can you explain in words, what exactly you want to accomplish?
Also please provide sample data described here: [url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Why use distinct in combination with group by?

It is bad practice to override the databases builtin locking mechanism (NOLOCK).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources