Table of ContentsPreviousNext

Ispirer             Ispirer


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.

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


Table of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.