Print      Open PDF Version of Online Help

Analytics > Designing an Analysis > 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. 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.