Geek asks and answers

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

I am 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 get the following error:

An aggregate may not appear in WHERE if it is not in the subquery contained in the HAVING clause or selection list, and the column is an aggregated external link, SELECT dbo. [PatRoster]. * FROM dbo. [PatRoster] WHERE PatID = @PatIDParam AND EffectiveEnrollmentDate IN (Select MAX (EffectiveEnrollmentDate))

+3
source share
5 answers

Your request is invalid - Select MAX(EffectiveEnrollmentDate)not completed; he should choose EffectiveEnrollmentDatesomewhere in this subquery.

, MAX() , IN - =.

+2

:

(Select MAX(EffectiveEnrollmentDate))

:

(Select MAX(EffectiveEnrollmentDate) FROM PatRoster)
+2

As the error message shows, the correct way to do this is by using a subquery:

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

In the subquery: there is (Select MAX(EffectiveEnrollmentDate))no source, it cannot refer to an external request

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

You cannot have agregate in an auxiliary query by the values ​​of an external query:

AND EffectiveEnrollmentDate IN (Select MAX(EffectiveEnrollmentDate))

What do you want to do:

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

More articles:


All Articles