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 include:
- CAST
- CHOOSE
- IFNULL
- INDEXCOL
- TO_DATETIME
- VALUEOF
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:
oThe
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.
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)
CHOOSE
This
function takes an arbitrary number of parameters and returns the first item in
the list that the user has permission to see. However, administrators must
model the column permissions in the Administration Tool to enable this
behavior. See "INDEXCOL"for an alternate method.
CHOOSE(expr1,
expr2, ..., exprN)
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
This
function tests if an expression evaluates to a null value, and if it does,
assigns the specified value to the expression.
IFNULL(expr,
value)
Where:
expr is the
expression to evaluate.
value is the
value to assign if the expression evaluates to a null value.
INDEXCOL
This
function 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 INDEXCOLexpressions specified in a derived logical column.
- Query Log and cache. The Logical SQL query with INDEXCOL function appears in the SQL string in the query log. But the logical request does not show the INDEXCOL function because the Oracle BI Server translates INDEXCOL to one of the expressions in its expression list in the logical request generator.
The query cache uses the resulting translated expression for cache hit
detection.
- Usage Tracking.Usage tracking inserts the Logical SQL query string with the INDEXCOL function.
- Security. As long as the user has the privileges to access the columns in the expression translated from INDEXCOL, then the query executes.
When the
first argument to INDEXCOL is a session variable and if a default expression is
expected to be returned even if the initialization block fails, then you should
set a default value for the session variable. Otherwise, the query fails
because the session variable has no value definition.
INDEXCOL(integer_literal,
expr_list)
Where:
expr_list
equals the following:
expr1 [,
expr_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 equivalent to expr1. If the literal
value is 1, then the value is equivalent to expr2, and so on.
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.
Company ABC has a geography dimension with the hierarchy Country, 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 C-2 shows the back-end database for Company
ABC.
USER_NAME
|
TITLE
|
GEO_LEVEL
|
CURRENCY
|
CURRENCY_COL
|
Bob
|
CEO
|
0
|
US Dollars
|
0
|
Harriet
|
Sales Manager
|
1
|
Japanese Yen
|
1
|
Jackson
|
Sales Manager
|
1
|
Japanese Yen
|
1
|
Mike
|
Sales Person
|
2
|
Japanese Yen
|
1
|
Jim
|
Sales Person
|
2
|
US Dollars
|
0
|
The
following steps illustrate one way to create a single query where each user
sees the top level to which they have access:
- The administrator creates a new session variable called GEOOGRAPHY_LEVEL that is populated by the following initialization block: SELECT GEO_LEVEL from T where USER_NAME = ':USER'.
This assumes
that the Oracle BI Server instance has the same user names.
- UsingSELECT 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 gets the same result and can drill down on Country to State as if he
had used SELECT Country, Revenue FROM Sales.
Jackson logs in and INDEXCOL
translates to the State column because the GEOGRAPHY_LEVEL session variable for
Jackson is 1. He gets the same result and can 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 theGEOGRAPHY_LEVEL session variable for
Mike is 2. He gets the same result and cannot drill down on City as if he had
used SELECT City, Revenue FROM Sales.
TO_DATETIME
This
function converts string literals of dateTime format to a DateTime data type.
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.
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.
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 namedprime_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. 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, use the following
syntax in Expression Builder or a filter:
"SalesSubjectArea"."Customer"."Region"
= VALUEOF(NQ_SESSION.REGION)
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 wildcard that matches all initialization block names. This lets you change
the structure of the initialization blocks in a localized manner without
impacting requests.
Reference : otn.oracle.com
