How To Use DECODE in Oracle

How To Use DECODE in Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system database oracle performance tuning oracle decode oracle decode in oracle database administrator database oracle case oracle dual oracle select oracle

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;

dba oracle dba database management system database oracle performance tuning oracle decode oracle decode in oracle database administrator database oracle case oracle dual oracle select oracle

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;

dba oracle dba database management system database oracle performance tuning oracle decode oracle decode in oracle database administrator database oracle case oracle dual oracle select oracle

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;

dba oracle dba database management system database oracle performance tuning oracle decode oracle decode in oracle database administrator database oracle case oracle dual oracle select oracle

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;

dba oracle dba database management system database oracle performance tuning oracle decode oracle decode in oracle database administrator database oracle case oracle dual oracle select oracle

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;

dba oracle dba database management system database oracle performance tuning oracle decode oracle decode in oracle database administrator database oracle case oracle dual oracle select oracle

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*