Table of ContentsPreviousNext

Ispirer             Ispirer


Ispirer SQLWays Database Migration Software

Conditional Expressions

This subsection describes functions which compare an expression to each search value one by one and return the corresponding result, in various databases and their conversion by SQLWays.

TABLE 33. Conditional Expressions
Database
Syntax
Description
Oracle
DECODE (comp_exp, 
search_exp1, result_exp1 [, 
search_expN, result_expN]...  
[, default_exp]) 
Compares expression (comp_exp) to each search value (search_exp1, ..., search_expN) one by one and if expression (comp_exp) is equal to search value then returns the corresponding result (result_exp1, ..., result_expN), if no match is found then returns default (default_exp) or if default is omitted then returns NULL.
 
comp_exp, search_exp and result_exp can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 data type and is in the same character set as the first result parameter.
 
The search_exp, result_exp and default_exp values can be derived from expressions.
 
The DECODE function automatically converts comp_exp and each search value (search_exp) to the data type of the first search value before comparing and also automatically converts the return value to the same data type as the first result (result_exp). If the first result has the CHAR data type or if the first result is null, then the function converts the return value to the VARCHAR data type.
 
The DECODE function considers two nulls to be equivalent. If comp_exp is null, then DECODE returns the result of the first search that is also null.
 
The maximum number of components in the DECODE function, including comp_exp, searches (search_exp1, ..., search_expN), results (result_exp1, ..., result_expN), and default (default_exp), is 255.
Example
DECODE (emp_type, 1, `clerk', 2, `book-keeper', `Unknown')
 
CASE comp_exp  
WHEN search_exp1 THEN 
result_exp1 [WHEN 
search_expN THEN 
result_expN]...  
[ELSE default_exp] END 
Compares expression (comp_exp) to each search value (search_exp1, ..., search_expN) one by one and if expression (comp_exp) is equal to search value then returns the corresponding result (result_exp1, ..., result_expN), if no match is found then returns default (default_exp) or if default is omitted then returns NULL.
 
All of the expressions (comp_exp, search_exp and result_exp) must be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type.
Example:
CASE emp_type
WHEN 1 THEN `clerk'
WHEN 2 THEN `book-keeper'
ELSE `Unknown'
END
CASE WHEN condition_exp1 
THEN result_exp1  
[WHEN condition_expN THEN 
result_expN]...  
[ELSE default_exp] END 
Searches from left to right until it finds an occurrence of condition (condition_exp) that is true, and then returns result_exp. If no condition is found to be true then returns default (default_exp) or if default is omitted then returns NULL.
Example:
CASE WHEN salary>500 THEN salary
WHEN salary<200 THEN 200
ELSE 500
END
MySQL
CASE comp_exp  
WHEN search_exp1 THEN 
result_exp1 [WHEN 
search_expN THEN 
result_expN]...  
[ELSE default_exp] END 
Compares expression (comp_exp) to each search value (search_exp1, ..., search_expN) one by one and if expression (comp_exp) is equal to search value then returns the corresponding result (result_exp1, ..., result_expN), if no match is found then returns default (default_exp) or if default is omitted then returns NULL.
 
The type of the return value is the same as the type of the first returned value (the expression after the first THEN).
Example:
 
 
CASE workgroup
WHEN A THEN `Administration'
WHEN B THEN `Book-keeping'
WHEN C THEN `Customer department'
ELSE `Others'
END
CASE WHEN condition_exp1 
THEN result_exp1  
[WHEN condition_expN THEN 
result_expN]...  
[ELSE default_exp] END 
Searches from left to right until it finds an occurrence of condition (condition_exp) that is true, and then returns result_exp. If no condition is found to be true then returns default (default_exp) or if default is omitted then returns NULL.
 
The type of the return value is the same as the type of the first returned value (the expression after the first THEN).
Example:
CASE
WHEN salary between 200 and 500 THEN 500
WHEN salary<200 THEN 200
ELSE salary
END
Microsoft SQL Server
CASE comp_exp  
WHEN search_exp1 THEN 
result_exp1 [WHEN 
search_expN THEN 
result_expN]...  
[ELSE default_exp] END 
Compares expression (comp_exp) to each search value (search_exp1, ..., search_expN) one by one and if expression (comp_exp) is equal to search value then returns the corresponding result (result_exp1, ..., result_expN), if no match is found then returns default (default_exp) or if default is omitted then returns NULL.
 
Return types: the highest precedence type from the set of types in result_exp and the optional default_exp
Example:
CASE level
WHEN 1 THEN `First level'
WHEN 2 THEN `Second level'
WHEN 3 THEN `Third level'
ELSE `Top level'
END
 
CASE WHEN condition_exp1 
THEN result_exp1  
[WHEN condition_expN THEN 
result_expN]...  
[ELSE default_exp] END 
Searches from left to right until it finds an occurrence of condition (condition_exp) that is true, and then returns result_exp. If no condition is found to be true then returning default (default_exp) or if default is omitted then returning NULL.
 
Return types: the highest precedence type from the set of types in result_exp and the optional default_exp.
Example:
CASE WHEN price IS NULL
THEN 'Not yet priced'
WHEN price < 10
THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20
THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END
IBM DB2
CASE comp_exp  
WHEN search_exp1 THEN 
{result_exp1 | NULL}  
[WHEN search_expN THEN 
{result_expN | NULL}]...  
[ELSE default_exp] END 
Compares expression (comp_exp) to each search value (search_exp1, ..., search_expN) one by one and if expression (comp_exp) is equal to search value then returns the corresponding result (result_exp1, ..., result_expN), if no match is found then returns default (default_exp) or if default is omitted then returns NULL.
Example:
CASE credit_limit
WHEN 100 THEN 'Low'
WHEN 5000 THEN 'High'
ELSE 'Medium'
END
CASE WHEN condition_exp1 
THEN {result_exp1 | NULL}  
[WHEN condition_expN THEN 
{result_expN | NULL}]...  
[ELSE default_exp] END 
Searches from left to right until it finds an occurrence of condition (condition_exp) that is true, and then returns result_exp. If no condition is found to be true then returns default (default_exp) or if default is omitted then returns NULL.
Example:
CASE
WHEN1>0 THEN 'true'
ELSE `false'
END


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.