Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle SQL case statement (Read 13526 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Oracle SQL case statement
29.08.07 at 11:57:41
 
Oracle SQL case statement
 
In Oracle 10g you can use conditions to change the value of a column. In the past only decodes could be used but in Oracle 10g you can use the case statement.
 
Examples:
Form 1 where you enter the condition in the when clauses:
 
select ename
, sal
, case  
  when sal > 2000 then 'high salary'
  when sal > 3000 then 'very high salary'
  else  'low salary'
  end
from scott.emp

 
Form 2 where the case matches records:
 
select ename
, sal
, case deptno
  when 10 then 'department 10'
  when 20 then 'department 20'
  else  'not department 10 or 20'
  end
from scott.emp
Back to top
 
« Last Edit: 29.07.08 at 14:11:44 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle case statement
Reply #1 - 20.05.08 at 12:02:48
 
Oracle PLSQL case statement
 
In Oracle PL/SQL the case statement is used as follows:
 
case n
  when 1 then Action1;
  when 2 then Action2;
  when 3 then Action3;
  else        ActionOther;
end case;
 
p := case n
      when 1 then Value1;
      when 2 then Value2;
      else OtherValue
      end;
Back to top
 
« Last Edit: 02.09.08 at 11:26:20 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle SQL case statement
Reply #2 - 29.07.08 at 14:41:39
 
PL/SQL Case example2:
 
...
begin
  --
  case  
  when p1 = 'E' or p1 = 'G' then
    raise e1;
  when p1 = 'V' then
    raise e2;
  end case;
exception
  ...

 
 

declare
  v varchar2(1);
begin
  v := case
        when mypackage.myfunction(123) > 567  then
          'Y'
         else
           'N'
         end;
end;
Back to top
 
« Last Edit: 02.09.08 at 11:29:04 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1