DECODE Function - NULL Issue - Oracle to SQL Server Migration

In Oracle, you can use DECODE function to evaluate a list of expressions, and if a value is matched return the corresponding result.

In SQL Server, you can use CASE expression that is also supported by Oracle. Note that NULL values in DECODE function and CASE expression are handled differently .

Oracle:

   -- Sample table
   CREATE TABLE cities
   (
      name VARCHAR2(70)
   );
 
   -- Sample data
   INSERT INTO cities VALUES (NULL);
   INSERT INTO cities VALUES ('Unknown');
   INSERT INTO cities VALUES ('San Francisco');
   INSERT INTO cities VALUES ('Los Angeles');

Now consider 2 statements:

   -- DECODE with NULL value in a WHEN condition 
   SELECT name, 
               DECODE(name, 
                             NULL, 'N/A', 
                             'Unknown', 'N/A', name) decoded
   FROM cities;
 
   -- DECODE without NULL condition
   SELECT name, 
               DECODE(name, 
                             'San Francisco', 'SFO', 
                             'Los Angeles', 'LAX') decoded
   FROM cities;

The queries return:

WHEN NULL is Never True in CASE Expression

When you convert DECODE to CASE expression, and there is NULL condition, you have to use searched CASE form.

SQL Server:

   -- Sample table
   CREATE TABLE cities
   (
      name VARCHAR(70)
   );
 
   -- Sample data
   INSERT INTO cities VALUES (NULL);
   INSERT INTO cities VALUES ('Unknown');
   INSERT INTO cities VALUES ('San Francisco');
   INSERT INTO cities VALUES ('Los Angeles');

Converting DECODE using the simple CASE form:

   -- WHEN NULL is never true 
   SELECT   name,
                 CASE name 
                  WHEN NULL THEN 'N/A'
                  WHEN 'Unknown' THEN 'N/A' 
                  ELSE name END decoded
   FROM cities;
 
   -- CASE without NULL condition
   SELECT name,
               CASE name 
                 WHEN 'San Francisco' THEN 'SFO' 
                 WHEN 'Los Angeles' THEN 'LAX'  END decoded
   FROM cities;

The results of the queries:

You can see that the first query containing WHEN NULL did not replace NULL value with 'N/A' like Oracle DECODE did.

You have to use the searched CASE form and IS NULL condition:

   -- IS NULL condition is now correct 
   SELECT name,
                 CASE 
                   WHEN name IS NULL THEN 'N/A' 
                   WHEN name = 'Unknown' THEN 'N/A' 
                   ELSE name END decoded
   FROM cities;

Now the NULL value was correctly replaced with 'N/A'.

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - February 2013.

You could leave a comment if you were logged in.