Conversion Functions

The conversion functions convert a value from one form to another. You can also use the VALUEOF function in a filter to reference the value of an Oracle BI system variable.

CAST

This function changes the data type of an expression or a null literal to another data type. For example, you can cast a customer_name (a data type of Char or Varchar) or birthdate (a datetime literal). The following are the supported data types to which the value can be changed:

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 must be a character string or another BIT string.

Use CAST to change to a DATE data type. Do not use TO_DATE.

The following describes unique characteristics of the CHAR and VARCHAR data types:

• Casting to a CHAR data type. You must use a size parameter. If you do not add a size parameter, a default of 30 is added. Syntax options appear in the following list:

  • The recommended syntax is:

    CAST(expr|NULL AS CHAR(n)) 

    For example:

    CAST(companyname AS CHAR(35))

  • You can also use the following syntax:

    CAST(expr|NULL AS data_type)

    For example:

    CAST(companyname AS CHAR)

    Note: If you use this syntax, the Oracle BI Server explicitly converts and stores as CAST(expr|NULL AS CHAR(30))

• Casting to a VARCHAR data type. You must use a size parameter. If you omit the size parameter, you cannot can save the change.

Examples:

CAST("Requisition Identification"."Req. Identifier" AS CHAR(40))

CAST("Submission Job Offer"."Sequence" AS VARCHAR(40))

CAST(AVG(“Candidate Counts”.”# Submissions”) AS DOUBLE)

CAST("Submission Dates"."Hire Start Date" AS DATE)

CAST(TIMESTAMPDIFF(SQL_TSI_DAY, "Submission Dates"."Submission Created Date", CURRENT_DATE) AS INTEGER)

IFNULL

This function tests if an expression evaluates to a null value, and if it does, assigns the specified value to the expression.

Syntax

IFNULL(expr, value) 

Where:

expr is the expression to evaluate.

value is the value to assign if the expression evaluates to a null value.

Example:

IFNULL("Submission Job Offer Flex Field”.”UDF1”, “Requisition Compensation Flex Fields”.”UDF1”)

This will return the value for Requisition Compensation UDF1 if the value for Submission Job Offer UDF1 is null.

TO_DATETIME

This function converts string literals of dateTime format to a DateTime data type.

Syntax

TO_DATETIME('string1', 'DateTime_formatting_string') 

Where:

string1 is the string literal you want to convert

DateTime_formatting_string is the DateTime format you want to use, such as yyyy.mm.dd hh:mi:ss. For this argument, yyyy represents year, mm represents month, dd represents day, hh represents hour, mi represents minutes, and ss represents seconds.

Example:

TO_DATETIME("Requisition Flex Fields"."UDF4", 'mm-dd-yyyy')

This will convert UDF4 to a date/time data type while also enabling the date format to be specified.