CASE in WHERE Clause?
-
I am trying to use CASe in a WHERE clause but I am getting syntax errors. Anyone know the proper way to restate this query?
select * from dyna.dtaTenant t
WHERE
(CASE WHEN t.DTGsTypeofTenant = '3' THEN t.DyKey NOT IN
(
SELECT dyna.ctlLinks.DestKey
FROM dyna.dtaTenant
INNER JOIN dyna.ctlLinks ON dyna.dtaTenant.DyKey = dyna.ctlLinks.SrcRecord
AND dyna.dtaTenant.ProjName = dyna.ctlLinks.ProjName
INNER JOIN dyna.dtaTenant AS dtaTenant_1 ON dyna.ctlLinks.DestKey = dtaTenant_1.DyKey
AND dyna.ctlLinks.ProjName = dtaTenant_1.ProjName
WHERE dtaTenant_1.DTGsTypeofTenant='3'
 
WHEN t.DTGsTypeofTenant ='O' THEN t.DyKey NOT IN
(
SELECT dyna.ctlLinks.SrcRecord
FROM dyna.dtaTenant
INNER JOIN dyna.ctlLinks ON dyna.dtaTenant.DyKey = dyna.ctlLinks.SrcRecord
AND dyna.dtaTenant.ProjName = dyna.ctlLinks.ProjName
INNER JOIN dyna.dtaTenant AS dtaTenant_1 ON dyna.ctlLinks.DestKey = dtaTenant_1.DyKey
AND dyna.ctlLinks.ProjName = dtaTenant_1.ProjName
WHERE dtaTenant_1.DTGsTypeofTenant='O'
 
ELSE NULL END)
TIA
Ryan
-
What you are attempting is really dynamic SQL and it can't be done via a CASE in a where clause.
You can have:
Where Table.SomeColumn = (CASE ... END)
You can't have:
Where (Dynamic column determined by CASE ... END) = SomeValue
[Edit] At second glance, it can probably be expressed like this:
select * from dyna.dtaTenant t
WHERE
( t.DyKey NOT IN
(
SELECT dyna.ctlLinks.DestKey
FROM dyna.dtaTenant
INNER JOIN dyna.ctlLinks ON dyna.dtaTenant.DyKey = dyna.ctlLinks.SrcRecord
AND dyna.dtaTenant.ProjName = dyna.ctlLinks.ProjName
INNER JOIN dyna.dtaTenant AS dtaTenant_1 ON dyna.ctlLinks.DestKey = dtaTenant_1.DyKey
AND dyna.ctlLinks.ProjName = dtaTenant_1.ProjName
WHERE dtaTenant_1.DTGsTypeofTenant='3'
)
AND t.DTGsTypeofTenant = '3' )
OR
( t.DyKey NOT IN
(
SELECT dyna.ctlLinks.SrcRecord
FROM dyna.dtaTenant
INNER JOIN dyna.ctlLinks ON dyna.dtaTenant.DyKey = dyna.ctlLinks.SrcRecord
AND dyna.dtaTenant.ProjName = dyna.ctlLinks.ProjName
INNER JOIN dyna.dtaTenant AS dtaTenant_1 ON dyna.ctlLinks.DestKey = dtaTenant_1.DyKey
AND dyna.ctlLinks.ProjName = dtaTenant_1.ProjName
WHERE dtaTenant_1.DTGsTypeofTenant='O'
)
AND t.DTGsTypeofTenant ='O'
)
-
for this you don't need "case" but correlated subquery
select * from dyna.dtaTenant t
where not exists
(
SELECT 1
FROM dyna.dtaTenant
INNER JOIN dyna.ctlLinks
ON dyna.dtaTenant.DyKey = dyna.ctlLinks.SrcRecord
AND dyna.dtaTenant.ProjName = dyna.ctlLinks.ProjName
INNER JOIN dyna.dtaTenant AS dtaTenant_1
ON dyna.ctlLinks.DestKey = dtaTenant_1.DyKey
AND dyna.ctlLinks.ProjName = dtaTenant_1.ProjName
where dtaTenant_1.DTGsTypeofTenant = t.DTGsTypeofTenant
and dyna.ctlLinks.DestKey = t.DyKey
)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply