OBIEE Coversion Functions

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.

Functions

Cast
IfNull

See Also

Value Of 

Use the VALUEOF function in a filter to reference the value of an Oracle BI repository variable. Repository variables are defined in the Oracle BI Server Administration Tool. You can use the VALUEOF function when you edit the SQL for a request from the Advanced tab in Oracle BI Answers.

Syntax

Variables should be used as arguments of the VALUEOF function. Refer to static repository variables by name. 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 need to 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 need to 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 a initialization block named Region Security, this is an example of the proper syntax to use:

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

The names of session variables need to be preceded by NQ_SESSION, separated by a period, and contained within parentheses, including the NQ_SESSION portion. If the variable name contains a space, enclose the name in double quotes ( " ). For example, to use the value of a session variable named REGION, this is an example of the proper syntax to use in an expression builder (filter):

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

Cast

Changes the data type of a value or a null value to another data type. Oracle BI Answers supports this function according to the SQL-92 specification.

Syntax

CAST (Expr|NULL AS type)

Where:

Expr

The expression to be cast. For example, you can cast a customer_name (a data type of Char or Varchar) or birth date (a datetime literal).

type

The data type to which the value should be changed. The following types are currently supported:

CHARACTER, VARCHAR, INTEGER, 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.

Example

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

---

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)

Where:

Expression

The expression to evaluate.

Value

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