Microsoft SQL Server and Oracle Functions and Expressions

Ispirer SQLWays Database Migration Software
Microsoft SQL Server and Oracle Functions and Expressions

This chapter provides detailed descriptions of the differences in functions and expressions used by Microsoft SQL Server and Oracle databases.

Oracle DECODE Expressions

In Oracle, DECODE expressions are used to easily manipulate the data representation of a table. SQLWays converts Oracle DECODE expressions to SQL Server simple CASE expressions.

For example, this query decodes the value DEPTNO. If DEPTNO is 10, the query returns 'ACCOUNTING'; if DEPTNO is 20, it returns 'RESEARCH'; etc. If DEPTNO is not 10, 20, 30, or 40, the query returns 'NONE'.
TABLE 87. DECODE Expression
Oracle

Microsoft SQL Server
 SELECT
DECODE (deptno,10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATION',
'NONE') name, loc 
FROM dept; 
SELECT 
CASE  deptno WHEN 10  THEN 'ACCOUNTING' 
WHEN 20  THEN 'RESEARCH' 
WHEN 30  THEN 'SALES'
WHEN 40  THEN 'OPERATION'
ELSE 'NONE' END name, loc 
FROM dept; 
For more information, see Oracle DECODE Expressions and Microsoft SQL Server Simple CASE Expressions