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.4.4
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.3 HotFix 1
- 6.4.3

Back Next

Example of a Searched CASE

SELECT name,
       CASE
          WHEN age < 0 THEN '(unknown)'
          WHEN age < 18 THEN 'Youth'
          WHEN age >= 18 AND age < 65 THEN 'Adult'
          ELSE 'Senior'
       END
FROM people;

In this example, the people
table is searched for name; as well, a character string that varies according to the age of the individual is returned with the name. Since ranges of ages are tested in the CASE statement in this example, as opposed to exact ages, a searched CASE statement is preferable to a simple CASE. Here, if the age
value is less than zero, the character string ‘(unknown)’ is returned; if the age is between 0 and 17 inclusive, ‘Youth’ is returned; and if the age is between 18 and 64 inclusive, ‘Adult’ is returned. If the age value does not fall into any of those ranges, then it must be 65 or greater, so the default value ‘Senior’ is returned instead. Note that the order of processing of the conditions can sometimes be important. In the above example, the condition “age < 0” must appear before “age < 18” in the CASE structure, otherwise a value of -1 will return ‘Youth’ instead of ‘(unknown)’.

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

Example of a Searched CASE

Example of a Searched CASE