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)
Go to Top of Page

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).
Go to Top of Page
   

- Advertisement -