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.
Author |
Topic |
musclebreast
Yak Posting Veteran
77 Posts |
Posted - 2014-06-08 : 18:29:43
|
HiI 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 ManagerFROM ree A1 inner join data A2 on A1.DataID=A2.ID AND A1.SubType=31066inner 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 subqueryIf I change the Field definition to:MAX(Case WHEN A2.AttrID = '23' AND A2.DefID = '9719132' THEN (A2.Valint) END) as Managerthen it works. The query Select Name from kua where ID=A2.Valinthas 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 KizerSQL Server MVP since 2007http://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 ManagerFROM dbo.Ree AS a1INNER JOIN dbo.Data AS a2 ON a2.ID = a1.DataIDINNER JOIN dbo.Ancestors AS an ON AN.DataID = a1.DataID AND an.AncestorID = 9735190LEFT JOIN dbo.Kua AS k ON k.ID = a2.ValIntWHERE a1.SubType = 31066;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|