How To Use DECODE in Oracle

This tutorial page is based on examples to make it easier to follow. Oracle DECODE allows to compare the input value with the following comparable values and every comparable value should have a result value. Among of all comparable values even empty (NULL) value is included into the usable value type list. You can use a Decode function inside another Decode function as many times you want but we would not recommend to do so and instead of you can use the Oracle CASE function. The main reason for that is readable using Oracle decode function in another Decode will be difficult to follow and the Oracle Case function is quite clear in this situation.

The syntax of Oracle Decode is :

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

The following Oracle DBA example is using Oracle DUAL table and the one-row-table value is “X“. Take a look at the following example below.

SELECT * FROM DUAL;

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

Using the table above out Oracle DBA has prepared an example with Oracle Decode function.

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

The SQL query returned “Dual Default” because the Dummy column has value “X”  as we did mention above and the first condition “NULL” didn’t match. The second value “X” did match and the Oracle Select statement returned the 2nd result “Dual Default“. The Decode in Oracle database takes first matching value and ignores rest, so the last result “Unknown” didn’t appear too.

The third Decode example has two “X” values declared as the comparable options. Let see what does Decode function now.

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

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

When none of the values don’t match and the default value is declared then Oracle Decode returns the last default value as in the following example below.

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

As we did mention before we don’t recommend to use Oracle Decode inside another Oracle Decode and this mainly because this way written statement is not clear enough and it is difficult to read. We did prepare the following two examples to compare the first one is with Oracle Decode in another Decode and the second example will get the same result using Oracle Case expression.

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

And now the same condition with Oracle Case expression.

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

Our Oracle DBA suggests to use the Oracle Case expression as you see on the last two examples it is much easier to read the complicated conditions when they are written with the Case than using Oracle Decode.

See Also:
Oracle Select Home