Monday, June 10, 2013

How to use CASE in WHERE clause Oracle

SELECT * FROM parts p
WHERE
case when slogic = 'begins' and partnum like sPartStrp||'%'
     then 1
     when slogic = 'contains' and partnum like '%'||spartStrp||'%'
     then 1
     when slogic = 'equals' and partnum = sPartStrp
     then 1
     when partnum like sPartStrp || '%'
     then 1
     else 0
 end = 1
and p.stock_type = 1

Labels: