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 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'.
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.