SQL Functions - Supported by MPP Engine | SQL Functions - Not supported by MPP Engine |
Analytical Functions | |
AVG | COVAR_POP |
COUNT | COVAR_SAMP |
CUME_DIST | CORR |
DENSE_RANK | LISTAGG |
FIRST_VALUE | NTH_VALUE |
LAG | PERCENTILE_CONT |
LAST_VALUE | PERCENTILE_DISC |
LEAD | RATIO_TO_REPORT |
MAX | REGR_AVGX |
MIN | REGR_AVGY |
NTILE | REGR_COUNT |
PERCENT_RANK | REGR_INTERCEPT |
RANK | REGR_R2 |
ROW_NUMBER | REGR_SLOPE |
SUM | REGR_SXX |
REGR_SYY | |
STDDEV | |
STDDEV_POP | |
STDDEV_SAMP | |
Array Functions | |
CARDINALITY | EXTEND |
CAST | FIND_INDEX |
CONCAT | TRUNCATE |
Binary Functions | |
INT1AND, INT2AND, INT4AND, INT8AND | |
INT1NOT, INT2NOT, INT4NOT, INT8NOT | |
INT1OR, INT2OR, INT4OR, INT8OR | |
INT1SHL, INT2SHL, INT4SHL, INT8SHL | |
INT1SHR, INT2SHR, INT4SHR, INT8SHR | |
INT1XOR, INT2XOR, INT4XOR, INT8XOR | |
Character Functions | |
ASCII | BITSTREAM_TO_BINARY |
BITCOUNT | CHR |
BIT_LENGTH | DLE_DST |
BTRIM | FIND |
CHAR_LENGTH | FIND_IN_SET |
CONCAT | GREATEST |
GET_JSON_OBJECT | INET_ATON |
HEX_TO_BINARY | INET_NTOA |
INSTR | INITCAP |
ISUTF8 | JSON_TABLES |
LOWER | LCASE |
LPAD | LEAST |
LTRIM | LEFT |
OCTET_LENGTH | LENGTH |
PARSE_URL | LE_DST |
POSITION | MD5 |
QUOTE_IDENT | OVERLAYB |
QUOTE_LITERAL | PARTIAL_STRING_MASK |
REGEXP_EXTRACT | REVERSE |
REGEXP_REPLACE | RIGHT |
REPEAT | SPLIT_PART |
REPLACE | TO_CANONICAL |
RPAD | TRANSLATE |
RTRIM | UCASE |
SPACE | UNICHR |
SOUNDEX | UNICODE |
STRPOS | V6_ATON |
SUBSTR | V6_NTOA |
TRIM | V6_SUBNETA |
TRIMBOTH | V6_SUBNETN |
TRIMLEADING | V6_TYPE |
TRIMTRAILING | |
UPPER | |
Conditional Functions | |
COALESCE | DECODE |
IFNULL | NVL2 |
ISNULL | |
ISNUMERIC | |
NULLIF | |
NVL | |
Convert Functions | |
CAST | TO_CHAR |
FORMAT_DATE | TRUNCATE |
PARSE_DATE | |
PARSE_TIME | |
PARSE_TIMESTAMP | |
TIMESTAMP | |
TO_BITSTRING | |
TO_DATE | |
TO_HEX | |
TO_NUMBER | |
TO_TIMESTAMP | |
TRUNC | |
Cryptographic Functions | |
HASHMD2 | |
HASHMD5 | |
HASHSHA | |
HASHSHA1 | |
Date Functions | |
CLOCK_TIMESTAMP | ADD_MONTHS |
CURRENT_DATE | AT_TIMEZONE |
CURRENT_TIME | DATEDIFF |
CURRENT_TIMESTAMP | DAYOFMONTH |
DATE_PART | DAYOFYEAR |
DATE_SUB | DB_TIMEZONE |
DATE_TRUNC | EXTRACTDOW |
DAY | EXTRACTDOY |
DAYOFWEEK | EXTRACTEPOCH |
DAYOFWEEK_ISO | EXTRACTMICROSECOND |
DAYS | EXTRACTMILLISECOND |
DAYS_BETWEEN | EXTRACTQUARTER |
EXTRACT | ISFINITE |
EXTRACTDAY | JULIAN_DAY |
EXTRACTHOUR | MICROSECOND |
EXTRACTMINUTE | MIDNIGHT_SECONDS |
EXTRACTSECOND | NUMTODSINTERVAL |
EXTRACTMONTH | NUMTOYMININTERVAL |
EXTRACTYEAR | QUARTER |
FROM_UNIXTIME | STATEMENT_TIMESTAMP |
GETUTCDATE | SYSDATE |
HOUR | TIMESTAMP_ROUND |
MINUTE | TIMESTAMP_TRUNC |
MONTH | TIMESTAMPADD |
MONTHS_BETWEEN | TIMESTAMPDIFF |
NEXT_DAY | TRANSACTION_TIMESTAMP |
NOW | WEEK |
SECOND | WEEK_ISO |
TIME_SLICE | |
TIMEOFDAY | |
TZ_OFFSET | |
TZCONVERTOR | |
UNIX_TIMESTAMP | |
UTC_TO_TIMESTAMP | |
YEAR | |
Numeric Functions | |
ABS | ATAN2 |
ACOS | DEGREES |
ASIN | LN |
ATAN | LOG10 |
CBRT | NUMERIC_LOG |
CEILING | ROWNUM |
COS | SINH |
COT | TANH |
EXP | |
FLOOR | |
LOG | |
MOD | |
PI | |
POW | |
POWER | |
RADIANS | |
RAND | |
RANDOM | |
ROUND | |
SIGN | |
SIN | |
SQRT | |
TAN | |
Phonetic Functions | |
DBL_MP | |
NYSIIS | |
PRI_MP | |
SCORE_MP | |
SEC_MP | |
XML Functions | |
XMLATTRIBUTES | XMLAGG |
XMLCOMMENT | |
XMLCONCAT | |
XMLDOCUMENT | |
XMLELEMENT | |
XMLFOREST | |
XMLNAMESPACES | |
XMLPI | |
XMLQUERY | |
XMLTEXT | |
XPATH | |
XSLT | |
Operator Functions | |
X + Y (Add) X + Y (Subtract) X * Y (Multiply) X/Y (Divide) X % Y (Modulus) -X (Negate) Note: If an expression that is computed has an undefined result (for example, 0/0), the MPP Engine returns the value “NaN”. For a similar scenario, the classic query engine throws an exception. The results may vary if the query is pushed down to a datasource. | FACTORIAL or X! X||Y (Concatenate) X ** Y (Exponentiate) |