Conversion Functions

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(hiredate AS CHAR(40)) FROM employee

SELECT CAST(hiredate AS VARCHAR(40)), CAST(age AS double precision), 
CAST(hiredate AS timestamp), CAST(age AS integer) FROM employee

CAST("db"."."table"."col" AS date)

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.

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.

Examples 

SELECT TO_DATETIME('2009-03-03 01:01:00', 'yyyy-mm-dd hh:mi:ss') FROM snowflakesales

SELECT TO_DATETIME('2009.03.03 01:01:00', 'yyyy.mm.dd hh:mi:ss') FROM snowflakesales

VALUEOF

Use the VALUEOF function to reference the value of a repository variable. Repository variables are defined using the Administration Tool. You can use the VALUEOF function both in Expression Builder in the Administration Tool, and when you edit the SQL statements for an analysis from the Advanced tab of the Analysis editor in Answers.

Syntax 

Variables should be used as arguments of the VALUEOF function. Refer to static repository variables by name. Note that variable names are case sensitive. For example, to use the value of a static repository variables named prime_begin and prime_end:

CASE WHEN "Hour" >= VALUEOF("prime_begin")AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END

You must refer to a dynamic repository variable by its fully qualified name. If you are using a dynamic repository variable, the names of the initialization block and the repository variable must be enclosed in double quotes ( " ), separated by a period, and contained within parentheses. For example, to use the value of a dynamic repository variable named REGION contained in an initialization block named Region Security, use the following syntax:

SalesSubjectArea.Customer.Region = VALUEOF("Region Security"."REGION")

The names of session variables must be preceded by NQ_SESSION, separated by a period, and contained within parentheses, including the NQ_SESSION portion. For example, to use the value of a session variable named REGION, use the following syntax in Expression Builder or a filter:

"SalesSubjectArea"."Customer"."Region" = VALUEOF(NQ_SESSION.REGION)

If the variable name contains or begins with any characters other than ASCII alphanumeric characters ([A-Z] [a-z] [0-9]) or an underscore (_), then enclose the name in double quotes ( " ). Examples of such characters include a space, single quote, or double quote. The following example contains a single quote and a space:

"SalesSubjectArea"."Customer"."Region" = VALUEOF("NQ_SESSION"."Steven's Regions")

If the variable name that you specify contains double quotes ("), then escape the double quotes with a set of double quotes. For example:

"SalesSubjectArea"."Customer"."Region" = VALUEOF("NQ_SESSION"."""Top Sales"" Region")