About the Data Vault SQL Reference
Introduction to SQL Reference for Informatica Data Vault
Date and Time Arithmetic
WHERE Clauses
UNION Operator
Parameterized Query
Functions
- Functions Overview
- ABS
- AVG
- CEILING
- CHAR
- COALESCE
- CONCAT
- COUNT
- DATE
- DATE (Date and Time)
- DAY
- DEC
- DIGITS
- EXP
- EXTRACT
- FLOOR
- HOUR
- IFNULL
- INT
- LASTDAY
- LEFT
- LEN
- LN
- LOG10
- LOWER
- LTRIM
- MAX
- MICROSECOND
- MIN
- MINUTE
- MOD
- MONTH
- NANOSECOND
- NULLIF
- PI
- POSITION
- POSSTR
- POWER
- REPLACE
- RIGHT
- ROUND
- RTRIM
- SECOND
- SIGN
- SQRT
- SUBSTRING
- SUM
- TIME
- TODAY
- TRIM
- UPPER
- YEAR

Data Vault SQL Reference

6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3 HotFix 1
- 6.4.3

Back Next

Searched CASE

In contrast to the simple CASE, the searched CASE does not use a single test expression to compare for equality. Instead, the searched CASE permits any Boolean condition, using any comparison operator(s), to appear in each WHEN clause. As in the simple CASE, the first condition to evaluate to true returns the value expression in the associated THEN clause. If no comparison evaluates to true, then the default value expression in the ELSE clause is returned. If there is no ELSE clause, a null value is returned. At least one of the THEN clauses must specify a value other than NULL. All comparisons must involve compatible datatypes.

Note that a simple CASE can always be rewritten as a searched CASE in the following manner:

You can rewrite it to be a simple CASE expression as follows:

CASE <value-exp-0>
  WHEN <compare-value-exp-1> THEN <return-value-exp-1>
  WHEN <compare-value-exp-2> THEN <return-value-exp-2>
  ...
  WHEN <compare-value-exp-n> THEN <return-value-exp-n>
  ELSE <value-exp-x>
END

You can rewrite it to be a searched CASE expression as follows:

CASE
  WHEN <value-exp-0> = <compare-value-exp-1> THEN <return-value-exp-1>
  WHEN <value-exp-0> = <compare-value-exp-2> THEN <return-value-exp-2>
  ...
  WHEN <value-exp-0> = <compare-value-exp-n> THEN <return-value-exp-n>
  ELSE <value-exp-x>
END

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

Searched CASE

Searched CASE