Function reference
Function components
Constants
FALSE
Example
NULL
Working with null values in Boolean expressions
Null values in comparison expressions
Null values in filter conditions
Nulls with operators
TRUE
Example
Operators
Operator precedence
Complex operators
Subscript operator (Arrays)
Subscript operator (Maps)
Dot operator (Structs)
Dot operator (Arrays of structs)
Complex operators for nested hierarchies
Arithmetic operators
String operators
Nulls
Example of string operators
Comparison operators
Comparing hierarchies
Logical operators
Nulls
Dates
Dates overview
Date/Time datatype
Julian Day, Modified Julian Day, and the Gregorian calendar
Dates in the year 2000
Dates in databases
Dates in flat files
Default date format
Date Format Strings
TO_CHAR format strings
Examples
TO_DATE and IS_DATE format strings
Requirements
Example
Understanding date arithmetic
Functions
Function overview
Aggregate functions
Conversion functions
Data cleansing functions
Date functions
Encoding functions
Financial functions
Horizontal expansion functions
Numeric functions
Scientific functions
Special functions
String functions
Test functions
Window functions
Function quick reference
Functions in advanced mode
%OPR_CONCAT%
%OPR_CONCATDELIM%
%OPR_IIF%
%OPR_SUM%
ABORT
ABS
ADD_TO_DATE
AES_DECRYPT
AES_ENCRYPT
AES_GCM_DECRYPT
AES_GCM_ENCRYPT
ASCII
AVG
CEIL
CHOOSE
CHR
CHRCODE
COMPRESS
CONCAT
CONVERT_BASE
COS
COSH
COUNT
CRC32
CUME
DATE_COMPARE
DATE_DIFF
DEC_BASE64
DECODE
DECOMPRESS
ENC_BASE64
ERROR
EXP
FIRST
FLOOR
FV
GET_DATE_PART
GREATEST
IIF
IN
INDEXOF
INITCAP
INSTR
IS_DATE
IS_NUMBER
IS_SPACES
ISNULL
LAG
LAST
LAST_DAY
LEAD
LEAST
LENGTH
LN
LOG
LOWER
LPAD
LTRIM
MAKE_DATE_TIME
MAX (Dates)
MAX (Numbers)
MAX (String)
MD5
MEDIAN
METAPHONE
MIN (Dates)
MIN (Numbers)
MIN (String)
MOD
MOVINGAVG
MOVINGSUM
NPER
PERCENTILE
PMT
POWER
PV
RAND
RATE
REG_EXTRACT
REG_MATCH
REG_REPLACE
REPLACECHR
REPLACESTR
REVERSE
ROUND (Dates)
ROUND (Numbers)
RPAD
RTRIM
SET_DATE_PART
SETCOUNTVARIABLE
SETMAXVARIABLE
SETMINVARIABLE
SETVARIABLE
SHA256
SIGN
SIN
SINH
SOUNDEX
SQRT
STDDEV
SUBSTR
SUM
SYSTIMESTAMP
TAN
TANH
TO_BIGINT
TO_CHAR (Dates)
TO_CHAR (Numbers)
TO_DATE
TO_DECIMAL
TO_FLOAT
TO_INTEGER
TRUNC (Dates)
TRUNC (Numbers)
UPPER
VARIANCE
System variables
CurrentMappingName
CurrentRunId
CurrentTaskId
CurrentTaskName
SESSSTARTTIME
SYSDATE
Datatype reference
Datatype reference overview
Rules and guidelines for datatypes
Transformation datatypes
Integer datatypes
Binary datatype
Date/Time data type
Decimal and double data types
String datatypes
Function reference
Transformation datatypes
HIDX_FUNCTYPE_CONVERSION
HIDX_FUNCTYPE_DATE
HIDX_FUNCTYPE_NUMERIC
HIDX_FUNCTYPE_SCI
HIDX_FUNCTYPE_SPECIAL
HIDX_FUNCTYPE_TEST
HIDX_FUNCPROTO_ABORT
HIDX_FUNC_ABORT
HIDX_FUNCDESC_ABORT
HIDX_FUNCPROTO_ABS
HIDX_FUNC_ABS
HIDX_FUNCDESC_ABS
HIDX_FUNC_ADD_TO_DATE
HIDX_FUNCDESC_ADD_TO_DATE
HIDX_FUNCPROTO_ADD_TO_DATE
HIDD_FUNC_AES_DECRYPT
HIDD_FUNC_AES_ENCRYPT
HIDD_FUNC_AES_GCM_DECRYPT
HIDD_FUNC_AES_GCM_ENCRYPT
HIDX_FUNC_ASCII
HIDX_FUNCPROTO_ASCII
HIDX_FUNCDESC_ASCII
HIDX_FUNC_AVG
HIDX_FUNCPROTO_AVG
HIDX_FUNCDESC_AVG
HIDX_FUNCDESC_CEIL
HIDX_FUNC_CEIL
HIDX_FUNCPROTO_CEIL
HIDD_FUNC_CHOOSE
HIDX_FUNCDESC_CHR
HIDX_FUNCPROTO_CHR
HIDX_FUNC_CHR
HIDX_FUNCPROTO_CHRCODE
HIDX_FUNCDESC_CHRCODE
HIDX_FUNC_CHRCODE
HIDD_FUNC_COMPRESS
HIDX_FUNC_CONCAT
HIDX_FUNCPROTO_CONCAT
HIDX_FUNCDESC_CONCAT
HIDD_FUNC_CONV
HIDX_FUNC_COS
HIDX_FUNCPROTO_COS
HIDX_FUNCDESC_COS
HIDX_FUNCDESC_COSH
HIDX_FUNC_COSH
HIDX_FUNCPROTO_COSH
HIDX_FUNC_COUNT
HIDX_FUNCPROTO_COUNT
HIDX_FUNCDESC_COUNT
HIDD_FUNC_CRC32
HIDX_FUNC_CUME
HIDX_FUNCPROTO_CUME
HIDX_FUNCDESC_CUME
HIDX_FUNC_DATE_COMPARE
HIDX_FUNCPROTO_DATE_COMPARE
HIDX_FUNCDESC_DATE_COMPARE
HIDX_FUNC_DATE_DIFF
HIDX_FUNCDESC_DATE_DIFF
HIDX_FUNCPROTO_DATE_DIFF
HIDD_FUNC_DEC_BASE64
HIDX_FUNC_DECODE
HIDX_FUNCDESC_DECODE
HIDX_FUNCPROTO_DECODE
HIDD_FUNC_DECOMPRESS
HIDD_FUNC_ENC_BASE64
HIDX_FUNC_ERROR
HIDX_FUNCDESC_ERROR
HIDX_FUNCPROTO_ERROR
HIDX_FUNC_EXP
HIDX_FUNCPROTO_EXP
HIDX_FUNCDESC_EXP
HIDX_FUNC_FIRST
HIDX_FUNCPROTO_FIRST
HIDX_FUNCDESC_FIRST
HIDX_FUNC_FLOOR
HIDX_FUNCDESC_FLOOR
HIDX_FUNCPROTO_FLOOR
HIDD_FUNC_FV
HIDX_FUNC_GET_DATE_PART
HIDX_FUNCDESC_GET_DATE_PART
HIDD_FUNC_GREATEST
HIDX_FUNCDESC_IIF
HIDX_FUNC_IIF
HIDD_FUNC_IN
HIDD_FUNC_INDEXOF
HIDX_FUNC_INITCAP
HIDX_FUNCDESC_INITCAP
HIDX_FUNCPROTO_INITCAP
HIDX_FUNC_INSTR
HIDX_FUNCDESC_INSTR
HIDX_FUNCPROTO_INSTR
HIDX_FUNCDESC_IS_DATE
HIDX_FUNCPROTO_IS_DATE
HIDX_FUNC_IS_DATE
HIDX_FUNCPROTO_IS_NUMBER
HIDX_FUNC_IS_NUMBER
HIDX_FUNCDESC_IS_NUMBER
HIDX_FUNCDESC_ISSPACES
HIDX_FUNCPROTO_ISSPACES
HIDX_FUNC_ISSPACES
HIDX_FUNC_ISNULL
HIDX_FUNCPROTO_ISNULL
HIDX_FUNCDESC_ISNULL
HIDX_FUNC_LAST
HIDX_FUNCPROTO_LAST
HIDX_FUNCDESC_LAST
HIDX_FUNCDESC_LAST_DAY
HIDX_FUNC_LAST_DAY
HIDX_FUNCPROTO_LAST_DAY
HIDD_FUNC_LEAST
HIDX_FUNCPROTO_LENGTH
HIDX_FUNC_LENGTH
HIDX_FUNCDESC_LENGTH
HIDX_FUNCPROTO_LN
HIDX_FUNC_LN
HIDX_FUNCDESC_LN
HIDX_FUNCDESC_LOG
HIDX_FUNC_LOG
HIDX_FUNCPROTO_LOG
HIDX_FUNCDESC_LOWER
HIDX_FUNCPROTO_LOWER
HIDX_FUNC_LOWER
HIDX_FUNCDESC_LPAD
HIDX_FUNC_LPAD
HIDX_FUNCPROTO_LPAD
HIDX_FUNCDESC_LTRIM
HIDX_FUNC_LTRIM
HIDX_FUNCPROTO_LTRIM
HIDD_FUNC_MAKE_DATE_TIME
HIDX_FUNCDESC_MAX
HIDX_FUNC_MAX
HIDX_FUNCPROTO_MAX
HIDD_FUNC_MD5
HIDX_FUNCDESC_MEDIAN
HIDX_FUNC_MEDIAN
HIDX_FUNCPROTO_MEDIAN
HIDX_FUNCDESC_METAPHONE
HIDX_FUNCDESC_MIN
HIDX_FUNC_MIN
HIDX_FUNCPROTO_MIN
HIDX_FUNCDESC_MOD
HIDX_FUNC_MOD
HIDX_FUNCPROTO_MOD
HIDX_FUNCDESC_MOVINGAVG
HIDX_FUNC_MOVINGAVG
HIDX_FUNCPROTO_MOVINGAVG
HIDX_FUNC_MOVINGSUM
HIDX_FUNCPROTO_MOVINGSUM
HIDX_FUNCDESC_MOVINGSUM
HIDD_FUNC_NPER
HIDX_FUNC_PERCENTILE
HIDX_FUNCDESC_PERCENTILE
HIDX_FUNCPROTO_PERCENTILE
HIDD_FUNC_PMT
HIDX_FUNCPROTO_POWER
HIDX_FUNCDESC_POWER
HIDX_FUNC_POWER
HIDD_FUNC_PV
HIDD_FUNC_RAND
HIDD_FUNC_RATE
HIDD_FUNC_REG_EXTRACT
HIDD_FUNC_REG_MATCH
HIDD_FUNC_REG_REPLACE
HIDX_FUNCDESC_REPLACECHR
HIDX_FUNC_REPLACECHR
HIDX_FUNCPROTO_REPLACECHR
HIDX_FUNCDESC_REPLACESTR
HIDX_FUNC_REPLACESTR
HIDX_FUNCPROTO_REPLACESTR
HIDD_FUNC_REVERSE
HIDX_FUNCPROTO_ROUND
HIDX_FUNC_ROUND
HIDX_FUNCDESC_ROUND2
HIDX_FUNCDESC_ROUND
HIDX_FUNC_ROUND2
HIDX_FUNCPROTO_ROUND2
HIDX_FUNC_RPAD
HIDX_FUNCDESC_RPAD
HIDX_FUNCPROTO_RPAD
HIDX_FUNCDESC_RTRIM
HIDX_FUNC_RTRIM
HIDX_FUNCPROTO_RTRIM
HIDX_FUNC_SET_DATE_PART
HIDX_FUNCPROTO_SET_DATE_PART
HIDX_FUNCDESC_SET_DATE_PART
HIDX_FUNC_SIGN
HIDX_FUNCDESC_SIGN
HIDX_FUNCPROTO_SIGN
HIDX_FUNCDESC_SIN
HIDX_FUNC_SIN
HIDX_FUNCPROTO_SIN
HIDX_FUNCPROTO_SINH
HIDX_FUNC_SINH
HIDX_FUNCDESC_SINH
HIDX_FUNCDESC_SOUNDEX
HIDX_FUNC_SQRT
HIDX_FUNCDESC_SQRT
HIDX_FUNCPROTO_SQRT
HIDX_FUNC_STDDEV
HIDX_FUNCDESC_STDDEV
HIDX_FUNCPROTO_STDDEV
HIDX_FUNCPROTO_SUBSTR
HIDX_FUNCDESC_SUBSTR
HIDX_FUNC_SUBSTR
HIDX_FUNC_SUM
HIDX_FUNCDESC_SUM
HIDX_FUNCPROTO_SUM
HIDX_FUNC_SYSTIMESTAMP
HIDX_FUNC_TAN
HIDX_FUNCDESC_TAN
HIDX_FUNCPROTO_TAN
HIDX_FUNC_TANH
HIDX_FUNCPROTO_TANH
HIDX_FUNCDESC_TANH
HIDX_FUNCDESC_TO_BIGINT
HIDX_FUNCDESC_TO_CHAR2
HIDX_FUNC_TO_CHAR
HIDX_FUNCPROTO_TO_CHAR
HIDX_FUNCDESC_TO_CHAR
HIDX_FUNCPROTO_TO_CHAR2
HIDX_FUNC_TO_CHAR2
HIDX_FUNCPROTO_TO_DATE
HIDX_FUNCDESC_TO_DATE
HIDX_FUNC_TO_DATE
HIDX_FUNCPROTO_TO_DECIMAL
HIDX_FUNCDESC_TO_DECIMAL
HIDX_FUNC_TO_DECIMAL
HIDX_FUNCDESC_TO_FLOAT
HIDX_FUNC_TO_FLOAT
HIDX_FUNCPROTO_TO_FLOAT
HIDX_FUNCPROTO_TO_INTEGER
HIDX_FUNCDESC_TO_INTEGER
HIDX_FUNC_TO_INTEGER
HIDX_FUNC_TRUNC
HIDX_FUNCPROTO_TRUNC
HIDX_FUNCDESC_TRUNC2
HIDX_FUNCPROTO_TRUNC2
HIDX_FUNC_TRUNC2
HIDX_FUNCDESC_TRUNC
HIDX_FUNCDESC_UPPER
HIDX_FUNC_UPPER
HIDX_FUNCPROTO_UPPER
HIDX_FUNCPROTO_VARIANCE
HIDX_FUNC_VARIANCE
HIDX_FUNCDESC_VARIANCE
HL_SystemVariables