How To Use Oracle DECODE Function
  Home
 

This page is based on examples to be easier to follow. The Oracle DECODE allows you to compare the input value with the following ones and every comparable value should have a returning value. Among of all values even empty (null) values are included to the list. Function Decode in oracle can be used in themselves as many times you want, but we would not to recommend to do so and instead of you could use Oracle CASE function. The main reason for that is that your code will be more readable, using Oracle Decode function in another Decode will be difficult to follow and Oracle Case function is more clear in this case.

The syntax is following:

SELECT DECODE(<input_value>,
              <value_to_compare_1>,<result_1>,
              <value_to_compare_1>,<result_1>,..,<default_result>)
 FROM <table_name> ;

The first example is showing the DUAL table content and this one-row-table’s value is “X“. See at the output below.

SELECT * FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

P.S. The DUAL table is a one-row table that is present in all Oracle databases.

Using the table above Online Tech Support has prepared the next examples below with Oracle DECODE or CASE.

SELECT DECODE(DUMMY,NULL,'Empty','X','Dual Default','Unknown')
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The query returns the “Dual Default” string because the Dummy column has value “X” and the first “NULL” condition did not match. The second value “X” did match and the Select query returned the 2nd result “Dual Default“. Oracle DECODE function takes first matching value and ignores rest, so default result “Unknown” didn’t appear either.

The 3rd example has two “X” value with different outputs. The DECODE function returns the first available value now.

SELECT DECODE(DUMMY,NULL,'Empty',
                'X','Dual Default',
                'X','Dual Default Again','Unknown')
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

As you see the same result as in the 2nd example. The reason is that Oracle DECODE function takes first matching value and ignores rest, even if the second value matching too.

When any of the values don’t match and the default value is declared then the DECODE returns the default value as on the following example.

SELECT DECODE(DUMMY,NULL,'Empty',
                'Y','Dual Default',
                'Z','Dual Default Again','Unknown')
 FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

As we did mention above we do not recommend to use a DECODE in another Oracle DECODE function and this is mainly about not being clear enough and it is difficult to read. As the following two examples will demonstrate the first with DECODE and the same result achieved using the Oracle CASE function.

SELECT DECODE(DUMMY,NULL,'Empty',
              DECODE(DUMMY,'X','Dual Default',
                       'X','Dual Default Again','Unknown')
              )
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

SELECT CASE DUMMY
         when NULL then 'Empty'
         when 'Y' then 'Dual Default'
         when 'X' then'Dual Default Again'
         else 'Unknown' END
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle decode oracle decode in oracle sql decode oracle sql decode in oracle sql oracle database software database oracle dual oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

We are thinking it’s much easier to read the SQL query when combined conditions are written within Oracle Case then with using many Oracle Decode functions.


See Also the following:
Home Oracle CASE

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*