Case statement in where clause (Help)

kamechi · May 14th, 2012, 01:03 AM

hi all..i need to write a query whereby using case statement in where clause...can anyone guide me? what i want is in my table i need to base on one column(co_role) to determine my where clause..eg:-

Code:

delete from co_employee where (co_salary > 5000 or co_transfer = 'Y') and co_department = 'HR' and co_role = 'Manager'
delete from co_employee where co_class = 'Contract' and co_salary > 2000 and co_department = 'HR' and co_role = 'Staff'

so i was thinking to write in this way (merge 2 in 1) but its not working:-

Code:

delete from co_employee where case (co_role) when 'Manager' then  ((co_salary > 5000 or co_transfer = 'Y') and co_department = 'HR') when 'Staff' then (co_class = 'Contract' and 'co_salary > 2000 and co_department = 'HR')
else null end

Thanks