Migrating DECODE Functions from Oracle to SQL Server

Use the CASE expression to replace the DECODE statement in your application code. This table shows the syntax for each statement.

Oracle	SQL Server
DECODE (test_value, expression1, value1 [[,expression2, value2] [...]] [,default_value] )	CASE test_value WHEN expression1 THEN value1 [[WHEN expression2 THEN value2] [...]] [ELSE default_value] END

Both the DECODE statement and the CASE expression perform conditional tests. When the value in test_value matches any following expression, the related value is returned. If no match is found, the default_value is returned. If no default_value is specified, both DECODE and CASE return NULL if there is no match. The following table shows an example of a converted DECODE statement.

Oracle	SQL Server
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(DECODE(grade ,'A', 4 ,'A+', 4.3 ,'A-', 3.7 ,'B', 3 ,'B+', 3.3 ,'B-', 2.7 ,'C', 2 ,'C+', 2.3 ,'C-', 1.7 ,'D', 1 ,'D+', 1.3 ,'D-', 0.7 ,0)),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN	CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND(AVG(CASE grade WHEN 'A' THEN 4 WHEN 'A+' THEN 4.3 WHEN 'A-' THEN 3.7 WHEN 'B' THEN 3 WHEN 'B+' THEN 3.3 WHEN 'B-' THEN 2.7 WHEN 'C' THEN 2 WHEN 'C+' THEN 2.3 WHEN 'C-' THEN 1.7 WHEN 'D' THEN 1 WHEN 'D+' THEN 1.3 WHEN 'D-' THEN 0.7 ELSE 0 END),2) FROM STUDENT_ADMIN.GRADE GROUP BY SSN

Oracle

SQL Server

CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(DECODE(grade
                    ,'A', 4
                    ,'A+', 4.3
                    ,'A-', 3.7
                    ,'B', 3
                    ,'B+', 3.3
                    ,'B-', 2.7
                    ,'C', 2
                    ,'C+', 2.3
                    ,'C-', 1.7
                    ,'D', 1
                    ,'D+', 1.3
                    ,'D-', 0.7
                    ,0)),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN

CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(CASE grade
                WHEN 'A' THEN 4
                WHEN 'A+' THEN 4.3
                WHEN 'A-' THEN 3.7
                WHEN 'B' THEN 3
                WHEN 'B+' THEN 3.3
                WHEN 'B-' THEN 2.7
                WHEN 'C' THEN 2
                WHEN 'C+' THEN 2.3
                WHEN 'C-' THEN 1.7
                WHEN 'D' THEN 1
                WHEN 'D+' THEN 1.3
                WHEN 'D-' THEN 0.7
                ELSE 0
                END),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN

The CASE expression supports the use of SELECT statements for performing Boolean tests, which the DECODE statement does not allow.

Migrating DECODE Functions from Oracle to SQL Server

See Also