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

BOOLAND()
GREATEST_IGNORE_NULLS()
NVL()
BOOLNOT()
IFF()
NVL2()
BOOLOR()
IFNULL()
REGR_VALX()
BOOLXOR()
LEAST()
REGR_VALY()
COALESCE()
LEAST_IGNORE_NULLS()
ZEROIFNULL()
EQUAL_NULL()
NULLIF()
-
GREATEST()
NULLIFZERO()
-

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_DOUBLE()
TO_BINARY()
TO_TIMESTAMP_LTZ()
TRY_TO_NUMBER()
TO_BOOLEAN()
TO_TIMESTAMP_NTZ()
TRY_TO_NUMERIC()
TO_CHAR()
TO_VARCHAR()
TRY_TO_TIMESTAMP()
TO_DECIMAL()
TRY_TO_BINARY()
TRY_TO_TIMESTAMP_LTZ()
TO_DOUBLE()
TRY_TO_BOOLEAN()
TRY_TO_TIMESTAMP_NTZ()
TO_NUMBER()
TRY_TO_DATE()
-
TO_NUMERIC()
TRY_TO_DECIMAL()
-

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_COUNT()
REGEXP_LIKE()
REGEXP_SUBSTR_ALL()
REGEXP_EXTRACT_ALL()
REGEXP_REPLACE()
RLIKE()
REGEXP_INSTR()
REGEXP_SUBSTR()
-

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()
-
For information on functions and expression syntax, see SQL function reference in the Snowflake documentation.