CODEWITHSUNDEEP
c#sql
Wesley
Wesley

Reputation: 307

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

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))

Upvotes: 3

Views: 3761

Answers (5)

manji
manji

Reputation: 47978

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)

Upvotes: 0

Alex K.
Alex K.

Reputation: 175876

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])

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

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

Upvotes: 0

Datajam
Datajam

Reputation: 4241

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 =.

Upvotes: 2

Ahmad Mageed
Ahmad Mageed

Reputation: 96507

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

(Select MAX(EffectiveEnrollmentDate))

Change it to:

(Select MAX(EffectiveEnrollmentDate) FROM PatRoster)

Upvotes: 2

Related Questions