An aggregate may not appear in the WHERE clause unless it is in a subquery

Question

I'm trying to use the following code:

m_Set.ClearQueryInfo();
m_Set.SetParameterWhere("PatID = @PatIDParam AND EffectiveEnrollmentDate IN (Select MAX(EffectiveEnrollmentDate))");
m_Set.SetWhere("PatID = ? AND EffectiveEnrollmentDate IN (Select MAX(EffectiveEnrollmentDate))");
m_Set.SetNumParams(1);
m_Set.SetParam("@PatIDParam", 1, PatIDParam.ToString());

but I end up receiving the following 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 aggregated is an outer reference, SELECT dbo.[PatRoster].* FROM dbo.[PatRoster] WHERE PatID = @PatIDParam AND EffectiveEnrollmentDate IN (Select MAX(EffectiveEnrollmentDate))

Answer 1

Your query is not valid - Select MAX(EffectiveEnrollmentDate) is not complete; it has to select EffectiveEnrollmentDate from somewhere in that subquery.

Also, MAX() only ever returns a single value, so there is no need for IN - you can just do straight comparison operator =.

Answer 2

You're not specifying the table source to query from here:

(Select MAX(EffectiveEnrollmentDate))

Change it to:

(Select MAX(EffectiveEnrollmentDate) FROM PatRoster)

Answer 3

As the error message suggests, the correct way to do this is with a subquery:

select bar.foo
from bar
where bar.foo = (select max(subbar.foo) from bar as subbar); -- subquery

Answer 4

The subquery: (Select MAX(EffectiveEnrollmentDate)) lacks a source, it cannot refer to the outer query

SELECT dbo.[PatRoster].* 
FROM dbo.[PatRoster] 
WHERE PatID = @PatIDParam AND EffectiveEnrollmentDate = (Select MAX(EffectiveEnrollmentDate) FROM dbo.[PatRoster])

Answer 5

You can't have an agregate in a sub query over the values of outer query:

AND EffectiveEnrollmentDate IN (Select MAX(EffectiveEnrollmentDate))

What you want to do is this:

SELECT dbo.[PatRoster].*
  FROM dbo.[PatRoster]
 WHERE PatID = @PatIDParam
   AND EffectiveEnrollmentDate = (Select MAX(EffectiveEnrollmentDate)
                                    FROM dbo.[PatRoster]
                                   WHERE PatID = @PatIDParam)