Snowflake Data Cloud Connector > Part III: SQL ELT with Snowflake Data Cloud Connector > Mappings in SQL ELT mode for Snowflake Data Cloud > Functions in mappings in SQL ELT mode
  

Functions in mappings in SQL ELT mode

When you create expressions within a mapping in SQL ELT mode, you must use the native functions and expression syntax of Snowflake Data Cloud and not Informatica functions and expression syntax.
You can use the following native functions in a mapping in SQL ELT mode:

Aggregate functions

ANY_VALUE()
COVAR_POP()
REGR_INTERCEPT()
APPROX_COUNT_DISTINCT()
COVAR_SAMP()
REGR_R2()
APPROX_PERCENTILE()
HASH_AGG()
REGR_SLOPE()
APPROX_PERCENTILE_ACCUMULATE()
HLL()
REGR_SXX()
APPROX_TOP_K()
HLL_ACCUMULATE()
REGR_SXY()
APPROX_TOP_K_ACCUMULATE()
HLL_COMBINE()
REGR_SYY()
ARRAY_UNIQUE_AGG()
KURTOSIS()
SKEW()
AVG()
MAX()
STDDEV()
BITAND_AGG()
MAX_BY()
STDDEV_POP()
BITOR_AGG()
MEDIAN()
STDDEV_SAMP()
BITXOR_AGG()
MIN()
SUM()
BOOLAND_AGG()
MIN_BY()
VAR_POP()
BOOLOR_AGG()
MINHASH()
VAR_SAMP()
BOOLXOR_AGG()
MODE()
VARIANCE()
CORR()
REGR_AVGX()
VARIANCE_POP()
COUNT()
REGR_AVGY()
VARIANCE_SAMP()
COUNT_IF()
REGR_COUNT()
-

Bitwise expression functions

BITAND()
BITSHIFTLEFT()
GETBIT()
BITNOT()
BITSHIFTRIGHT()
-
BITOR()
BITXOR()
-

Conditional expression functions

BETWEEN
GREATEST_IGNORE_NULLS()
LIKE ANY()
BOOLAND()
IFF()
NULLIF()
BOOLNOT()
IFNULL()
NULLIFZERO()
BOOLOR()
IN()
NVL()
BOOLXOR()
IS DISTINCT FROM
NVL2()
CASE
IS NULL
REGR_VALX()
COALESCE()
LEAST()
REGR_VALY()
EQUAL_NULL()
LEAST_IGNORE_NULLS()
ZEROIFNULL()
GREATEST()
LIKE ALL()
-

Context functions

ALL_USER_NAMES()
CURRENT_SCHEMA()
GETDATE()
CURRENT_ACCOUNT()
CURRENT_SCHEMAS()
INVOKER_ROLE()
CURRENT_AVAILABLE_ROLES()
CURRENT_SECONDARY_ROLES()
INVOKER_SHARE()
CURRENT_CLIENT()
CURRENT_SESSION()
IS_GRANTED_TO_INVOKER_ROLE()
CURRENT_DATABASE()
CURRENT_STATEMENT()
IS_ROLE_IN_SESSION()
CURRENT_DATE()
CURRENT_TIMESTAMP()
LAST_QUERY_ID()
CURRENT_IP_ADDRESS()
CURRENT_TRANSACTION()
LAST_TRANSACTION()
CURRENT_REGION()
CURRENT_USER()
LOCALTIMESTAMP()
CURRENT_ROLE()
CURRENT_VERSION()
SYSDATE()
CURRENT_ROLE_TYPE()
CURRENT_WAREHOUSE()
SYSTIMESTAMP()

Conversion functions

DATE()
TO_TIMESTAMP()
TRY_TO_DECIMAL()
TO_BINARY()
TO_TIMESTAMP_LTZ()
TRY_TO_DOUBLE()
TO_BOOLEAN()
TO_TIMESTAMP_NTZ()
TRY_TO_NUMBER()
TO_CHAR()
TO_VARCHAR()
TRY_TO_NUMERIC()
TO_DOUBLE()
TRY_TO_BINARY()
TRY_TO_TIMESTAMP()
TO_NUMBER()
TRY_TO_BOOLEAN()
TRY_TO_TIMESTAMP_LTZ()
TO_NUMERIC()
TRY_TO_DATE()
TRY_TO_TIMESTAMP_NTZ()

Cortex functions

SNOWFLAKE_CORTEX_COMPLETE()
SNOWFLAKE_CORTEX_EXTRACT_ANSWER()
SNOWFLAKE_CORTEX_SENTIMENT()
SNOWFLAKE_CORTEX_SUMMARIZE()
SNOWFLAKE_CORTEX_TRANSLATE()
SNOWFLAKE_CORTEX_TRY_COMPLETE()

Data generation functions

NORMAL()
SEQ2()
UUID_STRING()
RANDOM()
SEQ4()
ZIPF()
RANDSTR()
SEQ8()
-
SEQ1()
UNIFORM()
-

Date and time functions

ADD_MONTHS()
NEXT_DAY()
DATEADD()
PREVIOUS_DAY()
DATEDIFF()
QUARTER()
DATE_FROM_PARTS() or DATEFROMPARTS()
SECOND()
DATE_PART()
SYSDATE()
DATE_TRUNC()
TIMESTAMPADD()
DAY()
TIMESTAMPDIFF()
DAYNAME()
TIMESTAMP_LTZ_FROM_PARTS() or TIMESTAMPLTZFROMPARTS()
DAYOFMONTH()
TIMESTAMP_NTZ_FROM_PARTS() or TIMESTAMPNTZFROMPARTS()
DAYOFWEEK()
TIME_SLICE()
DAYOFWEEKISO()
TRUNC()
DAYOFYEAR()
WEEK()
HOUR()
WEEKISO()
LAST_DAY()
WEEKOFYEAR()
MINUTE()
YEAR()
MONTH()
YEAROFWEEK()
MONTHNAME()
YEAROFWEEKISO()
MONTHS_BETWEEN()
-

Encryption functions

DECRYPT()
DECRYPT_RAW()
ENCRYPT()
ENCRYPT_RAW()

Numeric functions

ABS()
DEGREES()
ROUND()
ACOS()
DIV0()
SIGN()
ACOSH()
DIV0NULL()
SIN()
ASIN()
EXP()
SINH()
ASINH()
FACTORIAL()
SQRT()
ATAN()
FLOOR()
SQUARE()
ATAN2()
LN()
TAN()
ATANH()
LOG()
TANH()
CBRT()
MOD()
TRUNCATE()
CEIL()
PI()
TRUNC()
COS()
POW()
WIDTH_BUCKET()
COSH()
POWER()
-
COT()
RADIANS()
-

Regular expression functions

REGEXP
REGEXP_INSTR()
REGEXP_SUBSTR()
REGEXP_COUNT()
REGEXP_LIKE()
REGEXP_SUBSTR_ALL()
REGEXP_EXTRACT_ALL()
REGEXP_REPLACE()
RLIKE()

String and binary functions

ASCII()
JAROWINKLER_SIMILARITY()
SHA1_BINARY()
BASE64_DECODE_BINARY()
LEFT()
SHA1_HEX()
BASE64_DECODE_STRING()
LEN()
SHA2()
BASE64_ENCODE()
LENGTH()
SHA2_BINARY()
BIT_LENGTH()
LIKE()
SHA2_HEX()
CHR()
LOWER()
SOUNDEX()
CHAR()
LPAD()
SOUNDEX_P123()
CHARINDEX()
LTRIM()
SPACE()
COLLATE()
MD5()
SPLIT()
COLLATION()
MD5_BINARY()
SPLIT_PART()
COMPRESS()
MD5_HEX()
STARTSWITH()
CONCAT()
MD5_NUMBER_LOWER64()
STRTOK()
CONCAT_WS()
MD5_NUMBER_UPPER64()
STRTOK_TO_ARRAY()
CONTAINS()
OCTET_LENGTH()
SUBSTR()
DECOMPRESS_BINARY()
PARSE_IP()
SUBSTRING()
DECOMPRESS_STRING()
PARSE_URL()
TRANSLATE()
EDITDISTANCE()
POSITION()
TRIM()
ENDSWITH()
REPEAT()
TRY_BASE64_DECODE_BINARY()
HASH()
REPLACE()
TRY_BASE64_DECODE_STRING()
HEX_DECODE_BINARY()
REVERSE()
TRY_HEX_DECODE_BINARY()
HEX_DECODE_STRING()
RIGHT()
TRY_HEX_DECODE_STRING()
HEX_ENCODE()
RPAD()
UNICODE()
ILIKE()
RTRIM()
UPPER()
INITCAP()
RTRIMMED_LENGTH()
-
INSERT()
SHA1()
-

Window functions

ANY_VALUE()
CUME_DIST()
REGR_AVGX()
APPROX_COUNT_DISTINCT()
DENSE_RANK()
REGR_AVGY()
APPROX_PERCENTILE()
FIRST_VALUE()
REGR_COUNT()
APPROX_PERCENTILE_ACCUMULATE()
HASH_AGG()
REGR_INTERCEPT()
APPROX_TOP_K()
HLL()
REGR_R2()
APPROX_TOP_K_ACCUMULATE()
HLL_ACCUMULATE()
REGR_SLOPE()
ARRAY_UNIQUE_AGG()
HLL_COMBINE()
REGR_SXX()
AVG()
KURTOSIS()
REGR_SXY()
BITAND_AGG()
LAG()
REGR_SYY()
BITOR_AGG()
LAST_VALUE()
ROW_NUMBER()
BITXOR_AGG()
LEAD()
STDDEV()
BOOLOR_AGG()
MAX()
STDDEV_POP()
BOOLAND_AGG()
MEDIAN()
STDDEV_SAMP()
BOOLXOR_AGG()
MIN()
SUM()
CONDITIONAL_CHANGE_EVENT()
MINHASH()
VAR_POP()
CONDITIONAL_TRUE_EVENT()
MODE()
VAR_SAMP()
CORR()
NTH_VALUE()
VARIANCE()
COUNT()
NTILE()
VARIANCE_POP()
COUNT_IF()
PERCENT_RANK()
VARIANCE_SAMP()
COVAR_POP()
RANK()
-
COVAR_SAMP()
RATIO_TO_REPORT()
-
When you configure a function in the Expression transformation, use the expression syntax of the function displayed in the Field Expression dialog box.
For more information on functions, see SQL function reference in the Snowflake documentation.