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

EXISTS Predicate

The [NOT] EXISTS predicate tests a subquery for the presence of a value. If the subquery returns at least one value, the EXISTS predicate evaluates to TRUE, while a NOT EXISTS predicate evaluates to FALSE. Its syntax is as follows:

Note that a correlated
subquery is supported with some restrictions:

A correlated EXISTS predicate cannot appear in the HAVING clause.

Correlation can be used in the WHERE and HAVING clauses of the subquery.

Only one correlated column is allowed in the subquery, but it can be part of multiple conditions in the WHERE and HAVING clauses.

For instance, the following clauses are allowed in an EXISTS subquery ('outer' is a table reference from the outer query):

WHERE outer.c1 != inner.c1
WHERE outer.c1 > 15 AND inner.c2 > outer.c1
WHERE outer.c1 > 15 AND outer.c1*3 <= inner.c1 OR outer.c1 = 0
WHERE outer.c1 > 15 ... HAVING MAX(outer.c1) > 40

But the following are disallowed:

WHERE outer.c1 > 15 AND outer.c2 <= inner.c2
WHERE outer.c1 > 15 ... HAVING MAX(outer.c2) > 10

Rename Saved Search

Table of Contents

Data Vault SQL Reference

Data Vault SQL Reference

EXISTS Predicate

EXISTS Predicate