Error wirh Max function - SQL Server Forums

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

Error wirh Max function

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-06-08 : 18:29:43

Hi

I have the following SQL:



SELECT 


MAX(Case WHEN A2.AttrID = '23' AND A2.DefID = '9719132' THEN (Select Name from kua where ID=A2.Valint) END) as Manager



FROM ree A1 inner join data A2 on A1.DataID=A2.ID 

AND 

A1.SubType=31066



inner join ancestors AN ON AN.DataID = A1.DataID and AN.AncestorID=9735190

Every time I run it i get this error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

If I change the Field definition to:

MAX(Case WHEN A2.AttrID = '23' AND A2.DefID = '9719132' THEN (A2.Valint) END) as Manager

then it works. The query

Select Name from kua where ID=A2.Valint

has only one field as result. Why do I get an error?

Kind regards,

Lara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-08 : 18:54:41

You get the error because the subquery is not allowed there. It doesn't know there's only one value being returned until runtime. Place the value into a variable and then use the variable in that spot.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-06-09 : 05:56:13

Hi thanks for your answer, now I remember, it was possible in Orcale:)

Is there another way how to use a subquery with an aggreate function? My problem is that my sql query should be a view and I can't use variables wihin a view.

Kind regards,

Lara

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-09 : 11:39:19

[code]SELECT MAX(CASE WHEN a2.AttrID = '23' AND a2.DefID = '9719132' THEN k.Name END) as Manager
FROM dbo.Ree AS a1
INNER JOIN dbo.Data AS a2 ON a2.ID = a1.DataID
INNER JOIN dbo.Ancestors AS an ON AN.DataID = a1.DataID
AND an.AncestorID = 9735190
LEFT JOIN dbo.Kua AS k ON k.ID = a2.ValInt
WHERE a1.SubType = 31066;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources