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

Back Next

IFNULL

Returns the first of two arguments that is not null. This is the same as calling the COALESCE function with only two arguments. As with the COALESCE function, the IFNULL arguments are evaluated in the order in which they are specified, and the result is null only if both arguments are null.

IFNULL Example

SELECT item, IFNULL(price, -1) AS Price
FROM inventory;

In this example, a SELECT statement returns a list each item and its associated price from the inventory
table. The IFNULL function is used here to intercept item prices that have not been set (and are therefore null) and return a value of -1
instead. For items whose price is not null, the IFNULL function returns the price value unchanged.

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

SELECT item, 
       CASE
          WHEN price IS NOT NULL THEN price
          ELSE -1 
       END
       AS Price
FROM inventory;

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

IFNULL

IFNULL

IFNULL Example