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

Back Next

COALESCE

Returns the first argument that is not null. The arguments are evaluated in the order in which they are specified. The result is null only if all the arguments are null.

COALESCE Example

SELECT lastname, 
       job_desc, 
       COALESCE(salary, contract, commission, subsistence)
FROM payroll;

In this example, a worker on the payroll is paid either a regular salary, contract pay, a commission, or subsistence wages. The COALESCE function returns the value for the appropriate pay type, assuming all but the applicable pay field store null values. If none of the pay types apply, a null value is returned.

The equivalent (searched) CASE expression for this example would be the following:

SELECT lastname, 
       job_desc, 
       CASE
          WHEN salary IS NOT NULL THEN salary
          WHEN contract IS NOT NULL THEN contract
          WHEN commission IS NOT NULL THEN commission
          WHEN subsistence IS NOT NULL THEN subsistence
          ELSE NULL
       END
FROM payroll;

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

COALESCE

COALESCE

COALESCE Example