SQLTeam.com | Weblogs | Forums

An aggregate may not appear in the WHERE clause unless - RESOLVED

SET @xml = CAST((SELECT Orgs.Name AS 'td', '', dbo_Orgs_1.Name AS 'td', '', Max(FSCIndexes.IndexRangeHigh) AS 'td', '', FSCIndexes.ContractFSCType AS 'td', '', Max(FSCIndexes.ExpirationDT) AS 'td'
FROM (FSCIndexes INNER JOIN Orgs AS dbo_Orgs_1 ON FSCIndexes.CarrierOrgId = dbo_Orgs_1.Id) INNER JOIN Orgs ON FSCIndexes.ParentOrgId = Orgs.Id
WHERE Max(FSCIndexes.ExpirationDT) > @currDate <---added
GROUP BY Orgs.Name, dbo_Orgs_1.Name, FSCIndexes.ContractFSCType
HAVING (((Max(FSCIndexes.IndexRangeHigh))<6))
ORDER BY Orgs.Name, dbo_Orgs_1.Name
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

The querty was working fine until I added this line:
WHERE Max(FSCIndexes.ExpirationDT) > @currDate <---added

Error: 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 referenced is an outer reference.

Can anyone give me a tip on how to resolve? Thanks See below

I was able to resolve...
HAVING (((Max(FSCIndexes.IndexRangeHigh))<6)) and Max(FSCIndexes.ExpirationDT) > @currDate

Thanks