AbhiJeet
asked on
case statement in where clause
Hi experts,
I have to rewrite the below main query to include the "not exist" clauses in where clause with the conditions mentioned in the commented line:
--main sql
SELECT DISTINCT al.levels_type, al.levels_code, l.name
FROM ctlvls al, ctsions cs, lvls l
WHERE cs.code = 123456
AND al.TYPE = cs.TYPE
AND al.code = cs.code
AND l.code = al.code
--not exist clause
--when l.active = 'Y'
AND NOT EXISTS
(SELECT 1
FROM table2 t2
WHERE t2.TYPE = al.levels_type AND t2.code = al.levels_code)
--when l.active = 'N'
AND NOT EXISTS
(SELECT 1
FROM table1 t1
WHERE t1.TYPE = al.levels_type AND t1.code = al.levels_code)
Can someone please help me in rewriting the query?
I have to rewrite the below main query to include the "not exist" clauses in where clause with the conditions mentioned in the commented line:
--main sql
SELECT DISTINCT al.levels_type, al.levels_code, l.name
FROM ctlvls al, ctsions cs, lvls l
WHERE cs.code = 123456
AND al.TYPE = cs.TYPE
AND al.code = cs.code
AND l.code = al.code
--not exist clause
--when l.active = 'Y'
AND NOT EXISTS
(SELECT 1
FROM table2 t2
WHERE t2.TYPE = al.levels_type AND t2.code = al.levels_code)
--when l.active = 'N'
AND NOT EXISTS
(SELECT 1
FROM table1 t1
WHERE t1.TYPE = al.levels_type AND t1.code = al.levels_code)
Can someone please help me in rewriting the query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
this seems to be wanting a second opinion for the same problem
ASKER
Perfect, that is exactly what i needed.
https://www.experts-exchange.com/questions/28985063/case-statement-in-where-clause-with-not-exist.html