Decode and Case in Oracle SQL

Decode

  • Helps to implement the if else functionality
  • Functionality is limited in Decode, what we can do it Decode, we can also do it Case
  • Decode can take number and string for comparison

Case

  • Helps to implement the if else functionality
  • Case is better than decode.
  • Case can take only 1 datatype for comparison

Table Dump

create table student_tab_temp0747
(
s_no number,
s_name varchar2(100),
s_mark number,
s_result varchar2(1)
);

insert into student_tab_temp0747 values (1,'Scott',85,'P');
insert into student_tab_temp0747 values (2,'Allen',30,'F');
insert into student_tab_temp0747 values (3,'Smith',45,'P');
insert into student_tab_temp0747 values (4,'Adams',25,'F');
insert into student_tab_temp0747 values (5,'James',55,null);

Decode and Case Examples :

select * from student_tab_temp0747;

SELECT
    s_no,
    s_name,
    s_mark,
    s_result,
    DECODE(s_result,'P','Pass','F','Failed','Result not available') as result_decode,
    CASE s_result
        when 'P' then 'Pass'
        when 'F' then 'Failed'
        else 'Result not available'
    end as result_case1,
    CASE 
        when s_result='P' then 'Pass'
        when s_result='F' then 'Failed'
        else 'Result not available'
    end as result_case2,
    CASE 
        when s_result='P' and s_mark>=60 then 'First Class'
        when s_result='P' and s_mark<60 then 'Second Class'
        when s_result='F' then 'Failed'
        else 'Result not available'
    end as result_class
FROM
    student_tab_temp0747;

Example 2 :

select decode(:input,'1','One',2,'Two','3','Three','Other') as result from dual;


SELECT
    CASE :input
        WHEN '1'  THEN
            'One'
        WHEN '2'  THEN
            'Two'
        WHEN '3'  THEN
            'Three'
        ELSE
            'Others'
    END AS result
FROM
    dual;

Leave a Comment