Oracle DECODE and CASE expressions

The use of the DECODE function in Oracle is to substitute a value or expression with another. The CASE expressions serve a similar purpose but can be used in a wider variety of scenarios.

Let's say you have a character field in the database and you want to select data from the table and display a more user-friendly value instead of a single character. You can use the DECODE function to specify the column or expression to be checked followed by a pair of values or expressions to be matched with a substitution value, and finally a substitution value to be used if none of the matching values or expressions matched. Here is an example:

A table r5translines containing a column trl_type has the value 'I' for goods issued (sent out), 'RETN' for goods returned (such as when a defective product is returned), and 'RECV' for goods received (such as goods received from a supplier). To display this information in a user-friendly form, the SELECT statement can be writted with a DECODE statement as follows:

SELECT DECODE(trl_type, 'I', 'Issue', 'RETN', 'Return', 'RECD', 'Received', 'Unknown') FROM r5translines;

You can also do this using a CASE expression as follows:

SELECT CASE trl_type WHEN 'I' THEN 'Issue' WHEN 'RETN' THEN 'Return' WHEN 'RECD' THEN 'Received' ELSE 'Unknown' END CASE FROM r5translines;

In another selection of data, let's say you want to display the string 'Incoming' for received or returned goods, and the string 'Outgoing' for goods issued. You can use a CASE expression in a slightly different manner as follows:

SELECT CASE WHEN trl_type IN ('RECD', 'RETN') THEN 'Incoming' ELSE 'Outgoing' END CASE FROM r5translines;