Online Tech Support

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;

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;

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;

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;

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;

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

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