Table of Contents

Search

  1. Preface
  2. Part 1: Introduction to Amazon Redshift connectors
  3. Part 2: Data Integration with Amazon Redshift V2 Connector
  4. Part 3: Data Integration with Amazon Redshift Connector

Amazon Redshift Connectors

Amazon Redshift Connectors

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 Amazon Redshift and not Informatica functions and expression syntax.
You can use the following native functions in a mapping in SQL ELT mode:

Aggregate and bit-wise aggregate functions

ANY_VALUE()
COUNT()
STDDEV_SAMP()
AVG()
LISTAGG()
SUM()
BIT_AND()
MAX()
VAR_POP()
BIT_OR()
MEDIAN()
VAR_SAMP()
BOOL_AND()
MIN()
-
BOOL_OR()
STDDEV_POP()
-

Conditional functions

COALESCE()
LEAST()
NVL2()
DECODE()
NULLIF()
-
GREATEST()
NVL()
-

Data type formatting functions

TEXT_TO_INT_ALT()
TO_CHAR()
TO_NUMBER()
TEXT_TO_NUMERIC_ALT()
TO_DATE()
-

Date and time functions

ADD_MONTHS()
DATE_PART_YEAR()
TIMEOFDAY()
CONVERT_TIMEZONE()
DATE_TRUNC()
TIMESTAMP_CMP()
DATE_CMP()
GETDATE()
TIMESTAMP_CMP_DATE()
DATE_CMP_TIMESTAMP()
INTERVAL_CMP()
TIMEZONE()
DATEADD()
LAST_DAY()
TRUNC()
DATEDIFF()
MONTHS_BETWEEN()
-
DATE_PART()
NEXT_DAY()
-

Hash functions

CHECKSUM()
FNV_HASH()
SHA1()
farmFingerprint64()
MD5()
SHA2()
FUNC_SHA1()
SHA()
MURMUR3_32_HASH()

Math functions

ABS()
DEGREES()
POW()
ACOS()
DEXP()
POWER()
ASIN()
DLOG1()
RADIANS()
ATAN()
DLOG10()
RANDOM()
ATAN2()
EXP()
ROUND()
CBRT()
FLOOR()
SIGN()
CEIL()
LN()
SIN()
CEILING()
LOG()
SQRT()
COS()
MOD()
TAN()
COT()
PI()
TRUNC()

String functions

ASCII()
LENGTH()
REVERSE()
BPCHARCMP()
LOWER()
RIGHT()
BTRIM()
LPAD()
RPAD()
BTTEXT_PATTERN_CMP()
LTRIM()
RTRIM()
CHAR_LENGTH()
OCTETINDEX()
SOUNDEX()
CHARACTER_LENGTH()
OCTET_LENGTH()
SPLIT_PART()
CHARINDEX()
QUOTE_IDENT()
STRPOS()
CHR()
QUOTE_LITERAL()
STRTOL()
COLLATE()
REGEXP_COUNT()
SUBSTRING()
CONCAT()
REGEXP_INSTR()
TEXTLEN()
CRC32()
REGEXP_REPLACE()
TRANSLATE()
DIFFERENCE()
REGEXP_SUBSTR()
TRIM()
INITCAP()
REPEAT()
UPPER()
LEFT()
REPLACE()
LEN()
REPLICATE()
For more information about the native functions and expression syntax of Amazon Redshift, see SQL functions reference in Amazon Redshift documentation.

0 COMMENTS

We’d like to hear from you!