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

Value Expressions

Value expressions can be used anywhere Data Vault Service SQL syntax calls for a value expression
, for example, in the projection list of the SELECT statement. A value expression is an argument that evaluates to a single numeric, character string, or date/time value (the datatype of the expression must be appropriate to the statement in which it is used). Value expression primaries
, which are the “building blocks” of the value expression argument, consist of direct column references, character or numeric constants, functions, the arithmetic operators +
, –
, *
, /
, and the string concatenation operator ||
. A value expression may also contain another value expression enclosed in parentheses.

Note that only numeric values may be part of an arithmetic operation; only character values can be used with the concatenation operator.

The value expression argument is described in the following syntax diagram:

column: The column argument specifies the name of a column belonging to the archived table.
constant: The constant argument specifies a character or numeric literal. A numeric value may be preceded by a unary operator (that is, a + or –) to indicate whether it is a positive or negative value. A character string must be surrounded by single quotes. Only numeric values may be part of an arithmetic operation; only character values can be used with the concatenation operator.
function: A function can be applied to all input values, resulting in a single output value.
scalar subquery: The scalar subquery argument must return a single value only. If more than one value is returned, an error condition is generated. If the subquery result set is empty, a null value is returned instead.
Note that the entire SELECT statement must be contained in parentheses. Also, a correlated subquery is not permitted at this time.