Problem
In an effort to centralize your domain code management and further normalize the structure of your
database tables, you want to clean up and convert some text-format business attributes to numeric
equivalents. This will enhance reporting capabilities and reduce data entry errors in the future.
Solution
Use the CASE function to translate business keys or other intelligent numeric keys to numeric codes that
are centrally stored in a domain code table. For example, the ORDERS table of the OE schema contains a
column ORDER_MODE that currently has four possible values, identified in Table 4-1.
Table 4-1. Mapping the Text in the ORDER_MODE Column to Numeric Values
Text (source column) Numeric (destination column)
Direct 1
Online 2
Walmart 3
Amazon 4
The second column of Table 4-1 contains the numeric value we want to map to for each of the
possible values in the ORDER_MODE column. Here is the SQL you use to add the new column to the table:

alter table orders add (order_mode_num number);

Oracle versions 9i and later include the CASE statement, which is essentially a way to more easily
execute procedural code within the confines of the typically non-procedural SQL command language.
The CASE statement has two forms: one for simpler scenarios with a single expression that is compared to
a list of constants or expressions, and a second that supports evaluation of any combination of columns
and expressions. In both forms, CASE returns a single result that is assigned to a column in the SELECT
query or DML statement.
The recipe solution using the simpler form of the CASE statement is as follows:

update orders
set order_mode_num =
case order_mode
when 'direct' then 1
when 'online' then 2
when 'walmart' then 3
when 'amazon' then 4
else 0
end
;

Once you run the UPDATE statement, you can drop the ORDER_MODE column after verifying that no
other existing SQL references it.

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

Leave a Reply