![]() |
|
Conversion FunctionsThe conversion functions convert a value from one form to another. CastChanges 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))) IfNullTests 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 7/3/2018 | Copyright © 2005, 2018, Oracle. All rights reserved. Legal Notices. |