Oracle^® Business Intelligence Server Administration Guide > Oracle BI Server SQL Reference > SQL Reference >

Conversion Functions

The conversion functions convert a value from one form to another.

Cast

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

NOTE:  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.

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 will be added. Syntax options appear in the following list:
  • It is recommended that you use the following syntax:

  CAST (expression|NULL AS CHAR(n) )

  For example, CAST (companyname AS CHAR(35) )

  • You can use the following syntax:

  CAST (expression|NULL AS datatype )

  For example, CAST (companyname AS CHAR )

  NOTE:  If you use this syntax, Oracle BI Server will explicitly convert and store as CAST (expression|NULL AS CHAR(30) )

• Casting to a VARCHAR data type. The Administration Tool requires that you use a size parameter. If you omit the size parameter, you cannot can save the change.

Choose

Takes an arbitrary number of parameters and returns the first item in the list that the user has permission to see. However, the Oracle BI Administrator must model the column permissions in the Administration Tool to enable this behavior. For a alternate method, refer to IndexCol.

Syntax:

CHOOSE (expression1, expression2, ..., expressionN)

For example, a single query can be written to return security-based revenue numbers for the entire organization. The function could look like the following:

choose(L1-Revenue, L2-Revenue, L3-Revenue, L4-Revenue)

If the user issuing this function has access to the column L1-Revenue, then that column value would be returned. If the user does not have visibility to the column L1-Revenue but does have visibility to L2-Revenue, then L2-Revenue is returned.

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)

IndexCol

IndexCol can use external information to return the appropriate column for the logged-in user to see. The Oracle BI Server handles this function in the following ways:

• ODBC Procedures. NQSGetLevelDrillability and NQSGenerateDrillDownQuery return the context-specific drill-down information based on the expression translated from IndexCol. This applies to both IndexCol expressions specified in the logical SQL query and IndexCol expressions specified in a derived logical column.
• Query Log and cache. The logical SQL with IndexCol function appears in the SQL string in the query log. But the logical request will not show the IndexCol function because Oracle BI Server will translate IndexCol to one of the expressions in its expression list in the logical request generator.

  NOTE:  The query cache will use the resulting translated expression for cache hit detection.

• Usage Tracking. Usage tracking will insert the logical SQL query string with the IndexCol function.
• Security. As long as the user has the privileges to access the column(s) in the expression translated from IndexCol, then the query will execute.

  When the first argument to IndexCol is a session variable and if a default expression is expected to be returned even if the init block fails, then the Oracle BI Administrator should set a default value for the session variable. Otherwise, the query will fail because the session variable has no value definition.

Syntax:

IndexCol( integer literal, expression_list )

Where:

expression_list equals expr1 [, expression_list ]

The IndexCol function takes in an integer literal value as its first argument, followed by a variable length expression list and translates to a single expression from the expression list. The literal value is the 0-based index of the expression in the expression list to translate to. Consider the following expression:

IndexCol( integer literal, expr1, expr2, ... )

If the literal value is 0, the above expression is the same as expr1. If the literal value is 1, then the value is the same as expr2, and so on.

NOTE:  The primary use case for IndexCol is for the first argument to contain a session variable. Specifying a constant literal would result in IndexCol always choosing the same expression.

Example With Hierarchy Levels

Company ABC has a geography dimension with the hierarchy Country of State, City. The CEO can access the Country level down to the City level, and the sales manager can access the State and City levels, and the sales people can only access the City level. Table 41 shows the backend database for Company ABC.

The following steps illustrate one way to create a single query where each user sees the top level to which they have access:

• The Oracle BI Administrator creates a new session variable GEOOGRAPHY_LEVEL that is populated by the initialization block: SELECT GEO_LEVEL from T where USER_NAME = ':USER'.

  This assume that the Oracle BI Server instance has the same user names.

• Using SELECT IndexCol( VALUEOF( NQ_SESSION.GEOGRAPHY_LEVEL ), Country, State, City ), Revenue from Sales, the following occurs:
  • Bob logs in and IndexCol translates to the Country column because the GEOGRAPHY_LEVEL session variable is 0. He will get the same result and be able to drill down on Country to State as if he had used SELECT Country, Revenue from Sale.
  • Jackson logs in and IndexCol translates to the State column because the GEOGRAPHY_LEVEL session variable for Jackson is 1. He will get the same result and be able to drill down on State to City as if he had used SELECT State, Revenue from Sales.
  • Mike logs in and IndexCol translates to the City column because the GEOGRAPHY_LEVEL session variable for Mike is 2. He will get the same result and won't be able to drill down on City as if he had used SELECT City, Revenue from Sales.

VALUEOF( )

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

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 an 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. 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)

NOTE:  Although using initialization block names with session variables (just as with other repository variables) may work, you should use NQ_SESSION. NQ_SESSION acts like a wild card that matches all initialization block names. This allows the Oracle BI Administrator to change the structure of the initialization blocks in a localized manner without impacting reports.