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 Database Conversion overview.

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