Tuesday, 30 May 2017

difference between case and decode function in Oracle SQL

Difference Between DECODE and CASE

Difference Between DECODE and CASE :- Both are used for conditional construct in SQL.

1. CASE can work with logical operators other than ‘=’ while DECODE performs an equality check only.
Example:- To display grade based on their salaries.

SQL>select ename, case when sal < 1000  then 'A'
            when (sal >=1000 and sal < 2000)  then 'B'
            when (sal >= 2000 and sal < 3000) then 'C'
            else 'D'
            end sal_grade
from emp where rownum < 6;

SMITH    800    A
ALLEN    1600    B
WARD    1250    B
JONES    2975    C
MARTIN    1250    B

2. CASE can work with predicates (like ,in etc) and searchable subqueries

DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.


SQL>select e.ename,case when e.job in ('PRESODENT')  then 'Boss'
                        when e.job in ('MANAGER')    then 'Managers'
                        else 'General Employees'
                        end emp_category
from emp e
where rownum < 6;

SMITH    General Employees
ALLEN    General Employees
WARD    General Employees
JONES    Managers
MARTIN  General Employees

3. CASE can work as a PL/SQL construct

DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.

SQL> declare
  2    grade char(1);
  3  begin
  4    grade := 'b';
  5    case grade
  6      when 'a' then dbms_output.put_line('excellent');
  7      when 'b' then dbms_output.put_line('very good');
  8      when 'c' then dbms_output.put_line('good');
  9      when 'd' then dbms_output.put_line('fair');
 10      when 'f' then dbms_output.put_line('poor');
 11      else dbms_output.put_line('no such grade');
 12    end case;
 13  end;
 14  /

PL/SQL procedure successfully completed.

CASE can even work as a parameter to a procedure call, while DECODE cannot.

SQL> var a varchar2(5);
SQL> exec :a := 'THREE';

PL/SQL procedure successfully completed.

SQL> create or replace procedure proc_test (i number)
  2  as
  3  begin
  4    dbms_output.put_line('output = '||i);
  5  end;
  6  /

Procedure created.

SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end);
output = 3

PL/SQL procedure successfully completed.

4. NULL handled in different way by both

select decode(null, null, 'NULL', 'NOT NULL') test from dual;


select case null when null then 'NULL'
              else 'NOT NULL'
                      end test
from dual;


Below case query will work same as decode

select case when null is null then 'NULL'
        else 'NOT NULL'
        end test
from dual


5. CASE expects datatype consistency, DECODE does not

Compare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error.

select decode(2,1,1,'2','2','3') t from dual;


select case 2 when 1 then 1
              when '2' then '2'
              else '3'
              end t
from dual;

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

6. CASE is ANSI SQL-compliant and decode is the Oracle proprietary

7. The DECODE is shorter and easier to understand than CASE.
select ename, decode (deptno, 10, 'Accounting',
                  20, 'Research',
                  30, 'Sales',
                                  'Unknown') as department
from   emp
where rownum < 6;

---------- ----------
SMITH    Research
ALLEN    Sales
WARD    Sales
JONES    Research
MARTIN    Sales

select ename, case deptno when 10 then 'Accounting'
              when 20 then 'Research'
              when 30 then 'Sales'
              else         'Unknown'
              end as department
from emp
where rownum < 6;

---------- ----------
SMITH    Research
ALLEN    Sales
WARD    Sales
JONES    Research
MARTIN    Sales

1 comment:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.

    Hurry-Up Enroll Now and get Free Access to the Entire Course


Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...