7

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

user964147
  • 729
  • 4
  • 10
  • 29
  • 1
    Nesting CASE statements is one option. I'm not familiar enough with Oracle to know if there's a better way though. – Anthony Grist Feb 22 '13 at 10:49
  • What do you mean by _it checks only one condition at a time_ As case statement should help..if not then use decode but it only does equality checks – asifsid88 Feb 22 '13 at 10:53
  • See http://stackoverflow.com/questions/14386881/using-if-else-in-oracle – glh Feb 23 '13 at 07:38
  • If any of the below are not working, could you add an example of this in the if-then-elseif type? – glh Feb 23 '13 at 07:43

3 Answers3

20

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;
ZKK
  • 531
  • 3
  • 10
1

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'
DazzaL
  • 21,638
  • 3
  • 49
  • 57
0

Look out for "Decode in Oracle" 

decode( expression , search , result [, search , result]... [, default] )

It is similar to "if-elseif-else"

Refer: http://www.techonthenet.com/oracle/functions/decode.php

NOTE: It does only equality checks..

asifsid88
  • 4,631
  • 20
  • 30