Please, note, that Ispirer SQLWays 6.0 is no longer supported and provided to clients.

You can try out automated conversion of databases and applications with Ispirer Toolkit for free. Download free trial.

Check out the relevant toolkit documentation.

Ispirer DB2 to SQL Server Migration overview.

Ispirer SQLWays Database Migration Software

DECODE Expression

Syntax

DECODE(expr, search, result {, search, result} [, default])

Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null.

The search, result, and default values can be derived from expressions.

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

SELECT DECODE (deptno,10, 'ACCOUNTING', 
20, 'RESEARCH', 
30, 'SALES', 
40, 'OPERATION', 
'NONE') name, loc
FROM dept; 

Equivalents in other databases

TABLE 81. Equivalents in other databases

IBM DB2	Simple CASE expression
Microsoft SQL Server	Simple CASE expression