1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

decode function

Discussion in 'SQL PL/SQL' started by bk_mathew, Oct 30, 2010.

  1. bk_mathew

    bk_mathew Active Member

    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    80
    Hi Gurus,

    I would like to know about the 'DECODE' function in SQL and the application of the same.

    Regards,

    Biju
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Biju,
    DECODE function in SQL is similar to IF..ELSE conditional statement. Its syntax is

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

    Here, 'expression' is compared with the 'search' value. If it is equal then 'result' is executed, else 'default' action is done.

    It may be extended to multiple 'search'(s) and 'results'.

    For example,

    SELECT DECODE (DEPARTMENT_ID,10,'MANAGER STAFF',20,'HR DEPTT',30,'ADMIN', 'UNDEFINED DEPTT')
    FROM DEPARTMENTS

    Above query displays the department name based on department id from DEPARTMENTS table. If it is 10, then it would display 'MANAGER STAFF', if it is 20, then 'HR DEPTT'. If it is 30, then 'ADMIN'. If none of the values matched among of 10,20 or 30, then 'UNDEFINED DEPTT'is displayed.

    I hope it clears you now !!
     
    bk_mathew likes this.
  3. bk_mathew

    bk_mathew Active Member

    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for the reply.

    Biju
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    With decode function we can update the multiple values.

    for example we want to update sal of emp table
    given criteria.
    deptno raise in sal
    10 1000
    20 2000
    30 3000

    SQL> UPDATE EMP SET SAL = DECODE(DEPTNO,10,SAL+1000,20, SAL+2000,30,SAL+3000);