| Print Open PDF Version of Online Help

#### Conversion Functions The conversion functions convert a value from one form to another. ### Cast Changes the data type of either a value or a null value to another data type. This change is required before using values of one data type in a function or operation that expects another data type. Syntax: CAST (expression|NULL AS datatype) The supported data types to which the value can be changed are the following: CHARACTER, VARCHAR, INTEGER, FLOAT, SMALLINT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, BIT, BIT VARYING Depending on the source data type, some destination types are not supported. For example, if the source data type is a BIT string, the destination data type has to be a character string or another BIT string. Example: The following first changes (CASTs) Annual Revenues to an INTEGER to remove the decimal places, and then to a CHARACTER so that it can be concatenated with a character literal (the text 'K'): CAST ( CAST ( Account."Account Revenue"/1000 AS INTEGER ) AS CHARACTER ) ||' K' If you use the CAST function, then the CAST function might return an extra space at the end of the value, for example: CAST(YEAR("Date Created".Date) as char) || '*' returns "2012 *" You can avoid this problem by specifying the length of the return value, for example: CAST(YEAR("Date Created".Date) as char(4))) ### IfNull Tests if an expression evaluates to a null value, and if it does, assigns the specified value to the expression. Syntax: IFNULL (expression, value) ### ValueOf( ) Use the VALUEOF function in an expression builder or filter to reference the value of a session variable. For more information, see Session Variables in Analytics. Session variables should be used as arguments of the VALUEOF function. Refer to session variables by name. Example: To use the value of a session variable named NQ_SESSION.CURRENT_YEAR: CASE WHEN "Year" > VALUEOF(NQ_SESSION.CURRENT_YEAR) THEN 'Future' WHEN ... ELSE...END You must refer to a session variable by its fully qualified name.

Published 5/23/2022 | Copyright © 2005, 2022, Oracle and/or its affiliates. Legal Notices.