Print      Open PDF Version of Online Help

Reports > Using Functions in Analyses > Conversion Functions

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 defined in Oracle CRM On Demand Answers. For more information, see Session Variables.

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.