How to use If condition inside Case statement?

Question

Below is my request. It's true?

SQL> select case when value in (1000) then null 2 when user in ('ABC') then user 3 when area in ('DENVER') then 4 if value = 2000 then 'Service1' 5 else value = 3000 then 'Service2' 6 end if 7 else null 8 end as num_code from service_usoc_ref; if prin = 2000 then 'Omaha' * ERROR at line 4: ORA-00905: missing keyword 

Please help me.

Answer 1

You can either put another case or use decoding (as @madhu suggested):

 select case when value in (1000) then null when user in ('ABC') then user when area in ('DENVER') then case when value = 2000 then 'Service1' when value = 3000 then 'Service2' end else null end as num_code from service_usoc_ref; 

Answer 2

It can help you.

  select case when value in (1000) then null when user in ('ABC') then user when area in ('DENVER') then decode( value, 2000 , 'Service1',3000 , 'Service2', NULL) num_code from service_usoc_ref;