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
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 subquery inside case?

Author  Topic 

asksql
Starting Member

11 Posts

Posted - 2008-06-08 : 22:52:25
Hello,

I am trying to do something like this:

select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )
END ) as TEST
from tableA


But the problem is that the part in bold returns more than one row..
i want it to be select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id
for every value of tableA.col.


How do I do this?

vaseem
Starting Member

1 Post

Posted - 2008-07-09 : 10:52:59
Hi,

there are cases where subquery returns more than one row in this case always use top 1 statement like below

select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select top 1 table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )END ) as TEST
from tableA

Vaseem


Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-09 : 12:48:55
I think you need MAX ....and Group by.
Go to Top of Page
   

- Advertisement -