Link to home
Start Free TrialLog in
Avatar of AbhiJeet
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?
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

this seems to be wanting a second opinion for the same problem
Avatar of AbhiJeet

ASKER

Perfect, that is exactly what i needed.