Using case within exists - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Using case within exists

Author

Topic

Eyespi20
Starting Member

10 Posts

Posted - 2009-05-28 : 14:03:05

I need to base a result set on a case condition, however when I write the query as


select x,y,z,a
from
tbl1 join tbl2 on tbl1.x=tbl2.x
where
z in (
case when @condition ='s' then (select x from abc where z=z)
when @condition='t' then (select x from abc where z=y)
when @condition='u' then (select x from abc where z=x) end)

I get a subquery returns more than 1 value error.

I thought to use exists, but can't get that syntax to work either.

Any suggestions?

Margaret
è¿é

DBARider
Starting Member

1 Post

Posted - 2009-05-28 : 14:14:56

hmmmm. what about this:

select x
, y
, z
, a
from tbl1
join tbl2
on tbl1.x = tbl2.x
where z in ( select x
from abc
where ( z = z
and @condition = 's'
)
or ( z = y
and @condition = 't'
)
or ( z = x
and @condition = 'u'
) )

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-28 : 14:15:32

or this:


select x,y,z,a
from
tbl1 join tbl2 on tbl1.x=tbl2.x
where
z in (
select x from abc where z = 
              case 
              when @condition = 's' then 'z'
              when @condition = 't' then 'y'
              when @condition = 'u' then 'x'
              end
       )

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 14:15:57

[code]SELECT x,
y,
z,
a
FROM tbl1
INNER JOIN tbl2 ON tbl2.x = tbl1.x
INNER JOIN (
SELECT x
FROM abc
WHERE z = z
AND @Condition = 's'

UNION

SELECT x
FROM abc
WHERE z = y
AND @Condition = 't'

UNION

SELECT x
FROM abc
WHERE z = x
AND @Condition = 'u'
) AS d ON d.x = z[/code]

E 12°55'05.63"
N 56°04'39.26"

Eyespi20
Starting Member

10 Posts

Posted - 2009-05-28 : 14:28:02

Thank you all -- excellent suggestions all of you TG's worked perfectly for this particular situation, but I can see where the others are valid and would work for other situations I might find myself in.

My thanks for your time and trouble to help me solve this!

Margaret
è¿é

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources