if-elseif-else 'condition' in oracle SQL [duplicate]

Question

I am wondering if there if possibility to achieve some thing like

'if-elseif-else' condition , i know there is a 'case-when-then-else' but it checks only one condition at a time (if i understand it correctly). How can i achieve if-elseif-else scenario in Oracle sql

Answer 1

You can use if/else using case statements like this.

SELECT ename, CASE WHEN sal = 1000 THEN 'Minimum wage'
                   WHEN sal > 1000 THEN 'Over paid'
                   ELSE 'Under paid'
              END AS "Salary Status"
FROM   emp;

Answer 2

i know there is a 'case-when-then-else' but it checks only one condition at a time

What you are describing is a SIMPLE case. Oracle has two case types: SIMPLE and SEARCHED (see here for more info http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm)

SIMPLE

case A
  when 1 then 'foo'
  when 2 then 'bar'
  else ..
end

SEARCHED

case
  when A=1 and B='A' then 'foo'
  when D + C =1 and B !='A' then 'Bar'
  else ..
end

you probably want to use a searched case. You can use them in PL/SQL or SQL. eg in SQL

select ..
  from table
 where case
         when A=1 and B='A' then 'foo'
         when D + C =1 and B !='A' then 'Bar'
         else ..
       end = 'foo'