SuiteQL Supported and Unsupported Functions

The following sections list the supported and unsupported functions when using SuiteQL.

• Supported Functions

• Unsupported Functions

Supported Functions

The following table lists and describes functions you can use when you run queries using SuiteQL.

Supported Function	Description
ABS	returns the absolute value of n
ACOS	returns the arc cosine of n
ADD_MONTHS	returns the date date plus integer months
APPROX_COUNT_DISTINCT	returns the approximate number of rows that contain distinct values of expr
ASCII	returns the decimal representation in the database character set of the first character of char
ASCIISTR	takes as its argument a string, or an expression that resolves to a string, in any character set and returns an ASCII version of the string in the database character set
ASIN	returns the arc sine of n
ATAN	returns the arc tangent of n
ATAN2	returns the arc tangent of n1 and n2
AVG	returns the average value of expr
BFILENAME	returns a BFILE locator that is associated with a physical LOB binary file on the server file system
BITAND	computes an AND operation on the bits of expr1 and expr2
CEIL	returns smallest integer greater than or equal to n
CHARTOROWID	converts a value from CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to ROWIDdatatype
CHR	returns the character having the binary equivalent to n as a VARCHAR2 value
COALESCE	returns the first non-null expr in the expression list
COMPOSE	takes as its argument a string, or an expression that resolves to a string, in any datatype, and returns a Unicode string in its fully normalized form in the same character set as the input
CONCAT	concatenates char1 and char2 into one string
CORR	returns the coefficient of correlation of a set of number pairs
CORR_K	calculates the Kendall's tau-b correlation coefficient
CORR_S	calculates the Spearman's rho correlation coefficient
COS	returns the cosine of n (an angle expressed in radians)
COSH	returns the hyperbolic cosine of n
COUNT	returns the number of rows returned by the query
COVAR_POP	returns the population covariance of a set of number pairs
COVAR_SAMP	returns the sample covariance of a set of number pairs
CURRENT_DATE	returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE
CURRENT_TIMESTAMP	returns the current date and time in the session time zone
DECODE	compares expr to each search value one by one. If expr is equal to a search, then the Oracle database returns the corresponding result
DECOMPOSE	takes as its argument a string in any datatype and returns a Unicode string after decomposition in the same character set as the input
DENSE_RANK	computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER
EMPTY_BLOB	returns an empty LOB locator
EMPTY_CLOB	returns an empty LOB locator
EXP	returns e raised to the nth power
FLOOR	returns largest integer equal to or less than n
FROM_TZ	converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value
GREATEST	returns the greatest of a list of one or more expressions
INITCAP	returns char, with the first letter of each word in uppercase, all other letters in lowercase
INSTR	searches string for substring
LAST_DAY	returns the date of the last day of the month that contains date
LEAST	returns the least of the list of exprs
LENGTH	returns the length of char
LENGTH2	returns the length of the specified string, using UCS2 code points
LENGTH4	returns the length of the specified string, using UCS4 code points
LENGTHB	returns the length of the specified string, using bytes instead of characters
LENGTHC	returns the length of the specified string, using Unicode complete characters
LN	returns the natural logarithm of n, where n is greater than 0
LOCALTIMESTAMP	returns the current date and time in the session time zone in a value of datatype TIMESTAMP
LOG	computes the logarithm of an expression
LOWER	returns char, with all letters in lowercase
LPAD	returns expr1, left-padded to length n characters with the sequence of characters in expr2
LTRIM	removes from the left end of char all of the characters contained in set
MAX	returns the maximum value of expr
MEDIAN	is an inverse distribution function that assumes a continuous distribution model. It takes a numeric or datetime value and returns the middle value or an interpolated value that would be the middle value after the values are sorted
MIN	returns the minimum value of expr
MOD	returns the remainder of n2 divided by n1. Returns n2 if n1 is 0
MONTHS_BETWEEN	returns the number of months between date1 and date2
NANVL	useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. This function is useful for mapping NaN values to NULL
NEW_TIME	returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date
NEXT_DAY	returns the date of the first weekday named by char that is later than the date
NLSSORT	returns the string of bytes used to sort char
NLS_INITCAP	returns char, with the first letter of each word in uppercase, all other letters in lowercase
NLS_LOWER	returns char, with all letters in lowercase
NLS_UPPER	returns char, with all letters in uppercase
NULLIF	compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1
NVL	lets you replace null (returned as a blank) with a string in the results of a query
NVL2	lets you determine the value returned by a query based on whether a specified expression is null or not null
ORA_HASH	computes a hash value for a given expression
POWER	returns n2 raised to the n1 power
RANK	calculates the rank of a value in a set of values
REGEXP_INSTR	extends the functionality of the INSTR function by letting you search a string for a regular expression pattern
REGEXP_REPLACE	extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern
REGEXP_SUBSTR	extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern
REMAINDER	returns the remainder of n2 divided by n1
REPLACE	returns char with every occurrence of search_string replaced with replacement_string
ROUND	returns n rounded to integer places to the right of the decimal point
ROW_NUMBER	analytic function that assigns a unique number to each row to which it is applied
RPAD	returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary
RTRIM	removes from the right end of char all of the characters that appear in set
SIGN	returns the sign of n
SIN	returns the sine of n (an angle expressed in radians)
SINH	returns the hyperbolic sine of n
SOUNDEX	returns a character string containing the phonetic representation of char
SQRT	returns the square root of n
SUBSTR	returns a portion of char, beginning at character position, substring_length characters long
SUM	returns the sum of values of expr. You can use it as an aggregate or analytic function
SYS_EXTRACT_UTC	extracts the UTC from a datetime value with time zone offset or time zone region name
TAN	returns the tangent of n (an angle expressed in radians)
TANH	returns the hyperbolic tangent of n
TO_BINARY_DOUBLE	returns a double-precision floating-point number
TO_BINARY_FLOAT	returns a single-precision floating-point number
TO_CHAR	(number) converts n to a value of VARCHAR2 datatype
TO_CLOB	converts NCLOB values in a LOB column or other character strings to CLOB values
TO_DATE	converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype
TO_MULTI_BYTE	TO_MULTI_BYTE returns char with all of its single-byte characters converted to their corresponding multibyte characters
TO_NCHAR	converts a character string, CHAR, VARCHAR2, CLOB, or NCLOB value to the national character set
TO_NCLOB	converts CLOB values in a LOB column or other character strings to NCLOB values
TO_NUMBER	converts expr to a value of NUMBER datatype
TO_SINGLE_BYTE	returns char with all of its multibyte characters converted to their corresponding single-byte characters
TO_TIMESTAMP	converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype
TO_TIMESTAMP_TZ	converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype
TRANSLATE	returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string
TRUNC	(number) returns n1 truncated to n2 decimal places
TZ_OFFSET	returns the time zone offset corresponding to the argument based on the date the statement is executed
UNISTR	takes as its argument a text literal or an expression that resolves to character data and returns it in the national character set
UPPER	returns char, with all letters in uppercase
VSIZE	returns the number of bytes in the internal representation of expr
WIDTH_BUCKET	lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size

For a list of supported built-in functions, see SuiteQL Supported Built-in Functions.

Unsupported Functions

The following table provides a list of functions that are not supported when you run queries using SuiteQL. It also provides an alternative function you can use for each unsupported function, if available.

Unsupported Function	Alternative Function (If Available)
BIT_LENGTH	—
CEILING	CEIL
CHAR	—
CHARINDEX	INSTR
CHAR_LENGTH	LENGTH
CHARACTER_LENGTH	LENGTH
CONVERT	—
COT	—
DATEDIFF	—
LCASE	LOWER
LEFT	SUBSTR
LOCATE	INSTR
POSITION	INSTR
REPEAT	—
RIGHT	SUBSTR
SUBSTRING	SUBSTR
UCASE	UPPER

Related Topics

SuiteQL

Using SuiteQL

SuiteQL Join Types

SuiteQL Syntax and Examples

SuiteQL Limitations and Exceptions

SuiteQL Supported Built-in Functions

General Notices