## SQLWays - 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

#### DECODE Expression

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

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

Migration to Microsoft SQL Server from Oracle
[Main] [Table of Contents]
Copyright © 1999-2003 Ispirer Systems. All Rights Reserved.
SQLWays™ www.ispirer.com.