D Logical SQL Reference

Syntax and Usage Notes for the SELECT Statement

The SELECT statement, or query specification, is the way to query a decision support system through the Oracle BI Server. A SELECT statement returns a table to the client that matches the query. It is a table in the sense that the results are in the form of rows and columns.

The SELECT statement is the basis for querying any structured query language (SQL) database. The Oracle BI Server accepts logical requests to query objects in a repository, and users (or query tools) make those logical requests with ordinary SQL SELECT statements. The server then translates the logical requests into physical queries against one or more data sources, combines the results to match the logical request, and returns the answer to the end user.

The SELECT statement in Logical SQL differs from standard SQL in that tables do not need to be joined. Any join conditions supplied in the query are ignored because the join conditions are predefined in the Oracle BI repository.

This section provides the basic syntax for the SELECT statement, as well as definitions for individual clauses. The syntax descriptions cover only basic syntax and features unique to the Oracle BI Server. For a more comprehensive description of SQL syntax, see a third-party reference book on SQL or a reference manual on SQL from your database vendors. For Oracle Database, see Oracle Database SQL Language Reference.

This section contains the following topics:

• Basic Syntax for the SELECT Statement

• Usage Notes

• Subquery Support

• SELECT List Syntax

• FROM Clause Syntax

• WHERE Clause Syntax

• GROUP BY Clause Syntax

• ORDER BY Clause Syntax

SELECT [DISTINCT] select_list
FROM from_clause
[WHERE search_condition]
[GROUP BY column {, column}
     [HAVING search_condition]]
[ORDER BY column {, column}]

IN, NOT IN
Any, >=Any, =Any, <Any, <=Any, <>Any
All, >=All, =All, <All,<=All, <>All
EXISTS, NOT EXISTS

...
* |
  (column | expr) [[AS] alias]
  {, (column | expr) [[AS] alias] }
...

IN, NOT IN
Any, >=Any, =Any, <Any, <=Any, <>Any
All, >=All, =All, <All,<=All, <>All
EXISTS, NOT EXISTS

ORDER BY col1 NULLS LAST, ORDER BY col2 NULLS FIRST

Syntax and Usage Notes for SELECT_PHYSICAL

The SELECT_PHYSICAL command provides the functionality to directly query objects in the Physical layer of the metadata repository, and to nest such a statement within a query against the Business Model and Mapping layer or the Presentation layer.

Though a SELECT_PHYSICAL query bypasses the Presentation layer and the Business Model and Mapping layer, the Oracle BI Server still performs parsing, interpretation, and query generation on a SELECT_PHYSICAL query before passing it to the database.

A SELECT_PHYSICAL command can contain any element allowed in standard Oracle BI Server SQL with the following constraints:

• The SELECT_PHYSICAL command does not explicitly reference structures in the repository Business Model and Mapping layer or the Presentation layer

• The SELECT_PHYSICAL command does not require implicit logical transformation

• The SELECT_PHYSICAL command cannot contain certain aggregate functions - see "Aggregate Functions Not Supported in SELECT_PHYSICAL Queries" for details

You can set up an ODBC connection to the Oracle BI Server to be a dedicated physical connection over which all SELECT queries are treated as SELECT_PHYSICAL queries. To do this, select Route Requests To Physical Layer in the ODBC data source for the Oracle BI Server. See "Integrating Other Clients with Oracle Business Intelligence" in Oracle Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

SELECT_PHYSICAL statements are logged as Physical Request entries.

The topics in this section are the following:

• Syntax for the SELECT_PHYSICAL Statement

• Aggregate Functions Not Supported in SELECT_PHYSICAL Queries

• Queries Supported by SELECT_PHYSICAL

• Using the NATURAL_JOIN Keyword

• Special Usages of SELECT_PHYSICAL

SELECT_PHYSICAL [DISTINCT] select_list
FROM from_clause
[WHERE search_condition]
[GROUP BY column {, column}
     [HAVING search_condition]]
[ORDER BY column {, column}]

SELECT PHYSICAL *
FROM A, B;

SELECT_PHYSICAL *
FROM A NATURAL JOIN B;

SELECT_PHYSICAL *
FROM C, A NATURAL JOIN B;

SELECT_PHYSICAL VALUEOF(NQ_SESSION.REGION) 
FROM "My_DB"."My_Schema"."products";

SELECT_PHYSICAL INDEXCOL(VALUEOF(NQ_SESSION.INDEXCOLINDEX), productid, categoryid)
FROM "My_DB"."My_Schema"."products";

Operators

There are two types of operators: SQL logical operators, and mathematical operators.

Conditional Expressions

Expressions are building blocks for creating conditional expressions that convert a value from one form to another. Expressions include:

• CASE (Switch)

• CASE (If)

CASE expr1
     WHEN expr2 THEN expr3
     {WHEN expr... THEN expr...}
     ELSE expr
END 

CASE Score-par
  WHEN -5 THEN 'Birdie on Par 6'
  WHEN -4 THEN 'Must be Tiger'
  WHEN -3 THEN 'Three under par'
  WHEN -2 THEN 'Two under par'
  WHEN -1 THEN 'Birdie'
  WHEN 0 THEN 'Par'
  WHEN 1 THEN 'Bogey'
  WHEN 2 THEN 'Double Bogey'
  ELSE 'Triple Bogey or Worse'
END

CASE 
     WHEN request_condition1 THEN expr1
     {WHEN request_condition2 THEN expr2}
     {WHEN request_condition... THEN expr...}
     ELSE expr
END 

CASE
  WHEN score-par < 0 THEN 'Under Par'
  WHEN score-par = 0 THEN 'Par'
  WHEN score-par = 1 THEN 'Bogie'
  WHEN score-par = 2 THEN 'Double Bogey'
  ELSE 'Triple Bogey or Worse'
END

Expressing Literals

A literal is a nonnull value corresponding to a given data type. Literals are typically constant values, or in other words, they are values that are taken as they are. A literal value must comply with the data type that it represents.

SQL provides mechanisms for expressing literals in SQL statements. This following topics describe how to express each type of literal in SQL:

• Character Literals

• Datetime Literals

• Numeric Literals

'Oracle BI Server'

'abc123'

DATE 'yyyy-mm-dd'
TIME 'hh:mm:ss'
TIMESTAMP 'yyyy-mm-dd hh:mm:ss'

DATE '2000-08-15'
TIME '11:55:25'
TIMESTAMP '1999-03-15 11:55:25'

234
+2
567934

1.223
-22.456
+33.456789

333.456E-
1.23e+

Variables

You can include and set variables in SQL statements. To do this, include the variable at the beginning of the SQL statement.

Syntax 

SET VARIABLE variable_name = variable_value; SELECT_statement

If you are executing a query from the nqcmd utility, use a colon as a delimiter. Otherwise, you can use either a semicolon or a colon.

Examples 

SET VARIABLE LOGLEVEL = 3; SELECT Products.Brand, Measures.Dollars FROM "Products"

SET VARIABLE DISABLE_CACHE_HIT=1, LOGLEVEL = 3, WEBLANGUAGE='en': SELECT
Products.Brand, Measures.Dollars FROM "Products"

Aggregate Functions

Aggregate functions perform operations on multiple values to create summary results.

Aggregate functions include:

• AGGREGATE AT

• AVG

• AVGDISTINCT

• BOTTOMN

• COUNT

• COUNTDISTINCT

• COUNT(*)

• MAX

• MEDIAN

• MIN

• NTILE

• PERCENTILE

• RANK

• STDDEV

• STDDEV_POP

• SUM

• SUMDISTINCT

• TOPN

AGGREGATE(expr AT level [, level1, levelN])

SELECT month, year, AGGREGATE(sales AT Year)FROM timeseriestestingWHERE year = 1994 AND month = 12

Month    Year    AGGREGATE AT year12       1994    7396Row count: 1

AVG(numExpr)

AVG(DISTINCT numExpr)

BOTTOMN(numExpr, integer)

COUNT(expr)

COUNT(DISTINCT expr)

COUNT(*)

SELECT COUNT(*) FROM Facts

200000000

MAX(numExpr)

MEDIAN(numExpr)

MIN(numExpr)

NTILE(numExpr, numTiles)

PERCENTILE(numExpr)

RANK(numExpr)

STDDEV([ALL | DISTINCT] numExpr)

STDDEV_POP([ALL | DISTINCT] numExpr)

SUM(numExpr)

SUM(DISTINCT numExpr)

TOPN(numExpr, integer)

Running Aggregate Functions

Running aggregate functions are similar to functional aggregates in that they take a set of records as input, but instead of outputting the single aggregate for the entire set of records, they output the aggregate based on records encountered so far.

This section describes the running aggregate functions supported by the Oracle BI Server. Functions include:

• MAVG

• MSUM

• RSUM

• RCOUNT

• RMAX

• RMIN

MAVG(numExpr, integer)

MSUM(numExpr, integer)

select month, revenue, MSUM(revenue, 3) as 3_MO_SUM from sales_subject_area

MONTH    REVENUE    3_MO_SUM
JAN      100.00     100.00
FEB      200.00     300.00
MAR      100.00     400.00
APRIL    100.00     400.00
MAY      300.00     500.00
JUNE     400.00     800.00
JULY     500.00     1200.00
AUG      500.00     1400.00
SEPT     500.00     1500.00
OCT      300.00     1300.00
NOV      200.00     1000.00
DEC      100.00     600.00

RSUM(numExpr)

RSUM(expression1 [BY expression2[, expression3[, ...]]])

SELECT month, revenue, RSUM(revenue) as RUNNING_SUM from sales_subject_area

MONTH    REVENUE    RUNNING_SUM
JAN      100.00     100.00
FEB      200.00     300.00
MAR      100.00     400.00
APRIL    100.00     500.00
MAY      300.00     800.00
JUNE     400.00     1200.00
JULY     500.00     1700.00
AUG      500.00     2200.00
SEPT     500.00     2700.00
OCT      300.00     3000.00
NOV      200.00     3200.00
DEC      100.00     3300.00

RCOUNT(expr)

RCOUNT(expression1 [BY expression2[, expression3[, ...]]])

select month, profit, RCOUNT(profit) from sales_subject_area where profit > 200

MONTH    PROFIT    RCOUNT(profit)
MAY      300.00    2
JUNE     400.00    3
JULY     500.00    4
AUG      500.00    5
SEPT     500.00    6
OCT      300.00    7

RMAX(expr)

RMAX(expression1 [BY expression2[, expression3[, ...]]])

SELECT month, profit, RMAX(profit) from sales_subject_area

MONTH    PROFIT    RMAX(profit)
JAN      100.00    100.00
FEB      200.00    200.00
MAR      100.00    200.00
APRIL    100.00    200.00
MAY      300.00    300.00
JUNE     400.00    400.00
JULY     500.00    500.00
AUG      500.00    500.00
SEPT     500.00    500.00
OCT      300.00    500.00
NOV      200.00    500.00
DEC      100.00    500.00

RMIN(expr)

RMIN(expression1 [BY expression2[, expression3[, ...]]])

select month, profit, RMIN(profit) from sales_subject_area

MONTH    PROFIT    RMIN(profit)
JAN      400.00    400.00
FEB      200.00    200.00
MAR      100.00    100.00
APRIL    100.00    100.00
MAY      300.00    100.00
JUNE     400.00    100.00
JULY     500.00    100.00
AUG      500.00    100.00
SEPT     500.00    100.00
OCT      300.00    100.00
NOV      200.00    100.00
DEC      100.00    100.00

Time Series Functions

Time series functions operate on time-oriented dimensions. The time series functions calculate AGO, TODATE, and PERIODROLLING functions based on user supplied calendar tables, not on standard SQL date manipulation functions.

To use time series functions on a particular dimension, you have to designate the dimension as a Time dimension and set one or more keys at one or more levels as chronological keys. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information.

Functions include:

• AGO

• PERIODROLLING

• TODATE

AGO(expr, [time_level], offset)

SELECT Year_ID, AGO(sales, year, 1)

PERIODROLLING(measure, x ,y [,hierarchy])

SELECT year, month, day, PERIODROLLING(AGGREGATE(sales AT month), -1)

SELECT Month_ID, PERIODROLLING(monthly_sales, -1, 1)

SELECT Month_ID, PERIODROLLING(monthly_sales, -UNBOUND, 2)

SELECT Month_ID, PERIODROLLING(monthly_sales, -UNBOUND, UNBOUND)

SELECT year, month, country, city, PERIODROLLING(sales, -1, 1)
WHERE month in ('Mar', 'Apr') AND city = 'New York' 

SELECT year, fiscal_year, month, PERIODROLLING(sales, -1, 1, "fiscal_time_hierarchy")

TODATE(expr, time_level)

SELECT Year_ID, Month_ID, TODATE(sales, year)

ASCII

This function converts a single character string to its corresponding ASCII code, between 0 and 255. If the character expression evaluates to multiple characters, the ASCII code corresponding to the first character in the expression is returned.

Syntax 

ASCII(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

BIT_LENGTH

This function returns the length, in bits, of a specified string. Each Unicode character is 2 bytes in length (equal to 16 bits).

Syntax 

BIT_LENGTH(strExpr)

Where:

strExpr is any expression that evaluates to character string.

CHAR

This function converts a numeric value between 0 and 255 to the character value corresponding to the ASCII code.

Syntax 

CHAR(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value between 0 and 255.

CHAR_LENGTH

This function returns the length, in number of characters, of a specified string. Leading and trailing blanks are not counted in the length of the string.

Syntax 

CHAR_LENGTH(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

CONCAT

There are two forms of this function. The first form concatenates two character strings. The second form uses the character string concatenation character to concatenate more than two character strings.

Syntax for Form 1 (To Concatenate Two Strings) 

CONCAT(strExpr1, strExpr2)

Where:

strExprs are expressions that evaluate to character strings, separated by commas.

Example 

This example request returns the results shown.

SELECT DISTINCT CONCAT('abc', 'def') FROM employee
CONCAT('abc', 'def')

Result:

abcdef

Syntax for Form 2 (To Concatenate More Than Two Strings) 

CONCAT(strExpr1, strExpr2 || strExpr3)

Where:

strExprs are expressions that evaluate to character strings, separated by commas and the character string concatenation operator || (double vertical bars). First, strExpr2 is concatenated with strExpr3 to produce an intermediate string, then both strExpr1 and the intermediate string are concatenated by the CONCAT function to produce the final string.

Example 

This example request returns the results shown.

SELECT DISTINCT CONCAT('abc','def' || 'ghi') FROM employee

Result:

abcdefghi

INSERT

This function inserts a specified character string into a specified location in another character string.

Syntax 

INSERT(strExpr1, integer1, integer2, strExpr2)

Where:

strExpr1 is any expression that evaluates to a character string. Identifies the target character string.

integer1 is any positive integer that represents the number of characters from the beginning of the target string where the second string is to be inserted.

integer2 is any positive integer that represents the number of characters in the target string to be replaced by the second string.

strExpr2 is any expression that evaluates to a character string. Identifies the character string to be inserted into the target string.

Example 

In the first string, starting at the second position (occupied by the number 2), three characters (the numbers 2, 3, and 4) are replaced by the string abcd.

SELECT INSERT('123456', 2, 3, 'abcd') FROM table

Result:

1abcd56
1abcd56
...

LEFT

Returns a specified number of characters from the left of a string.

Syntax 

LEFT(strExpr, integer)

Where:

strExpr is any expression that evaluates to a character string.

integer is any positive integer that represents the number of characters from the left of the string to return.

Example 

This example returns the three leftmost characters from the character string 123456:

SELECT LEFT('123456', 3) FROM table

Result:

123
123
...

LENGTH

This function returns the length, in number of characters, of a specified string. The length is returned excluding any trailing blank characters.

Syntax 

LENGTH(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

LOCATE

This function returns the numeric position of a character string in another character string. If the character string is not found in the string being searched, the function returns a value of 0.

If you want to specify a starting position to begin the search, use the LOCATEN function instead. See "LOCATEN" for details.

Syntax 

LOCATE(strExpr1, strExpr2)

Where:

strExpr1 is any expression that evaluates to a character string. Identifies the string for which to search.

strExpr2 is any expression that evaluates to a character string. Identifies the string to be searched.

Examples 

This example returns 4 as the numeric position of the letter d in the character string abcdef:

Locate('d', 'abcdef')

This example returns 0, because the letter g is not found within the string being searched.

Locate('g', 'abcdef')

LOCATEN

This function returns the numeric position of a character string in another character string. LOCATEN is identical to the LOCATE function, except that the search begins at the position specified by an integer argument. If the character string is not found in the string being searched, the function returns a value of 0. The numeric position to return is determined by counting the first character in the string as occupying position 1, regardless of the value of the integer argument.

Syntax 

LOCATEN(strExpr1, strExpr2, integer)

Where:

strExpr1 is any expression that evaluates to a character string. Identifies the string for which to search.

strExpr2 is any expression that evaluates to a character string. Identifies the string to be searched.

integer is any positive (nonzero) integer that represents the starting position to begin to look for the character string.

Examples 

This example returns 4 as the numeric position of the letter d in the character string abcdef. The search begins with the letter c, the third character in the string. The numeric position to return is determined by counting the letter 'a' as occupying position 1.

LOCATEN('d' 'abcdef', 3)

This example returns 0, because the letter b occurs in the string before the starting position to begin the search.

LOCATEN('b' 'abcdef', 3)

LOWER

This function converts a character string to lowercase.

Syntax 

LOWER(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

OCTET_LENGTH

This function returns the number of bits, in base 8 units (number of bytes), of a specified string.

Syntax 

OCTET_LENGTH(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

POSITION

This function returns the numeric position of strExpr1 in a character expression. If strExpr1 is not found, the function returns 0. See also "LOCATE" and "LOCATEN" for related information.

Syntax 

POSITION(strExpr1 IN strExpr2)

Where:

strExpr1 is any expression that evaluates to a character string. Identifies the string to search for in the target string.

strExpr2 is any expression that evaluates to a character string. Identifies the target string to be searched.

Examples 

This example returns 4 as the position of the letter d in the character string abcdef:

POSITION('d', 'abcdef')

This example returns 0 as the position of the number 9 in the character string 123456, because the number 9 is not found.

POSITION('9', '123456')

REPEAT

This function repeats a specified expression n times.

Syntax 

REPEAT(strExpr, integer)

Where:

strExpr is any expression that evaluates to a character string.

integer is any positive integer that represents the number of times to repeat the character string.

Example 

This example repeats abc four times:

REPEAT('abc', 4)

REPLACE

This function replaces one or more characters from a specified character expression with one or more other characters.

Syntax 

REPLACE(strExpr1, strExpr2, strExpr3)

Where:

strExpr1 is any expression that evaluates to a character string. This is the string in which characters are to be replaced.

strExpr2 is any expression that evaluates to a character string. This second string identifies the characters from the first string that are to be replaced.

strExpr3 is any expression that evaluates to a character string. This third string specifies the characters to substitute into the first string.

Example 

In the character string abcd1234, the characters 123 are replaced by the character string zz:

Replace('abcd1234', '123', 'zz')

Result:

abcdzz4

RIGHT

This function returns a specified number of characters from the right of a string.

Syntax 

RIGHT(strExpr, integer)

Where:

strExpr is any expression that evaluates to a character string.

integer is any positive integer that represents the number of characters from the right of the string to return.

Example 

This example returns the three rightmost characters from the character string 123456:

SELECT right('123456', 3) FROM table

Result:

456

SPACE

This function inserts blank spaces.

Syntax 

SPACE(integer)

Where:

integer is any positive integer that indicates the number of spaces to insert.

SUBSTRING

This function creates a new string starting from a fixed number of characters into the original string.

Syntax 

SUBSTRING(strExpr FROM starting_position)

Where:

strExpr is any expression that evaluates to a character string.

starting_position is any positive integer that represents the number of characters from the start of the left side of the string where the result is to begin.

TRIMBOTH

This function strips specified leading and trailing characters from a character string.

Syntax 

TRIM(BOTH character FROM strExpr)

Where:

character is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default.

strExpr is any expression that evaluates to a character string.

TRIMLEADING

This function strips specified leading characters from a character string.

Syntax 

TRIM(LEADING character FROM strExpr)

Where:

character is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default.

strExpr is any expression that evaluates to a character string.

TRIMTRAILING

This function strips specified trailing characters from a character string.

Syntax 

TRIM(TRAILING character FROM strExpr)

Where:

character is any single character. If you omit this specification (and the required single quotes), a blank character is used as the default.

strExpr is any expression that evaluates to a character string.

UPPER

This function converts a character string to uppercase.

Syntax 

UPPER(strExpr)

Where:

strExpr is any expression that evaluates to a character string.

ABS

This function calculates the absolute value of a numeric expression.

Syntax 

ABS(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

ACOS

This function calculates the arc cosine of a numeric expression.

Syntax 

ACOS(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

ASIN

This function calculates the arc sine of a numeric expression.

Syntax 

ASIN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

ATAN

This function calculates the arc tangent of a numeric expression.

Syntax 

ATAN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

ATAN2

This function calculates the arc tangent of y/x, where y is the first numeric expression and x is the second numeric expression.

Syntax 

ATAN2(numExpr1, numExpr2)

Where:

numExpr is any expression that evaluates to a numeric value.

CEILING

This function rounds a noninteger numeric expression to the next highest integer. If the numeric expression evaluates to an integer, the CEILING function returns that integer.

Syntax 

CEILING(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

COS

This function calculates the cosine of a numeric expression.

Syntax 

COS(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

COT

This function calculates the cotangent of a numeric expression.

Syntax 

COT(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

DEGREES

This function converts an expression from radians to degrees.

Syntax 

DEGREES(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

EXP

This function sends the value to the power specified.

Syntax 

EXP(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

EXTRACTBIT

This function retrieves a bit at a particular position in an integer. It returns an integer of either 0 or 1 corresponding to the position of the bit. The primary use case for this function is to extract 'cell status' in the Hyperion Financial Management cube source. The EXTRACTBIT function cannot be pushed into any database, and is always internally executed (in the Oracle BI Server).

Syntax

Int ExtractBit(Arg1, Arg2)

Where:

Arg1 is an expression of the following types: INT, SMALLINT, UNIT, SMALLUNIT, TINYINT, TINYUNIT. If Arg1 is of double type, it is necessary to cast the column to an INT first.

Arg2 is an expression of type integer. The value should range from 1 to length_of_Arg1. 1 retrieves the Least Significant Bit. If the Arg2 is beyond the length of the integer, then 0 is returned. An error message is triggered when the Arg2 is less than 1.

FLOOR

This function rounds a noninteger numeric expression to the next lowest integer. If the numeric expression evaluates to an integer, the FLOOR function returns that integer.

Syntax 

FLOOR(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

LOG

This function calculates the natural logarithm of an expression.

Syntax 

LOG(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

LOG10

This function calculates the base 10 logarithm of an expression.

Syntax 

LOG10(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

MOD

This function divides the first numeric expression by the second numeric expression and returns the remainder portion of the quotient.

Syntax 

MOD(numExpr1, numExpr2)

Where:

numExpr is any expression that evaluates to a numeric value.

Examples 

This example request returns a value of 0:

MOD(9, 3)

This example request returns a value of 1:

MOD(10, 3)

PI

This function returns the constant value of pi (the circumference of a circle divided by its diameter).

Syntax 

PI()

POWER

This function takes the first numeric expression and raises it to the power specified in the second numeric expression.

Syntax 

POWER(numExpr1, numExpr2)

Where:

numExpr1 is any expression that evaluates to a numeric value.

RADIANs

This function converts an expression from degrees to radians.

Syntax 

RADIANS(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

RAND

Returns a pseudo-random number between 0 and 1.

Syntax 

RAND()

RANDFROMSEED

Returns a pseudo-random number based on a seed value. For a given seed value, the same set of random numbers are generated.

Syntax 

RAND(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

ROUND

This function rounds a numeric expression to n digits of precision.

Syntax 

ROUND(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer that represents the number of digits of precision.

Example 

This example returns 2.17 as the result.

ROUND(2.166000, 2)

SIGN

This function returns the following:

• A value of 1 if the numeric expression argument evaluates to a positive number.

• A value of -1 if the numeric expression argument evaluates to a negative number.

• 0 (zero) if the numeric expression argument evaluates to zero.

Syntax 

SIGN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

SIN

This function calculates the sine of a numeric expression.

Syntax 

SIN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

SQRT

This function calculates the square root of the numeric expression argument. The numeric expression must evaluate to a nonnegative number.

Syntax 

SQRT(numExpr)

Where:

numExpr is any expression that evaluates to a nonnegative numeric value.

TAN

This function calculates the tangent of a numeric expression.

Syntax 

TAN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

TRUNCATE

This function truncates a decimal number to return a specified number of places from the decimal point.

Syntax 

TRUNCATE(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer that represents the number of characters to the right of the decimal place to return.

Examples 

This example returns 45.12:

TRUNCATE(45.12345, 2)

This example returns 25.12:

TRUNCATE(25.126, 2)

CURRENT_DATE

This function returns the current date. The date is determined by the system in which the Oracle BI Server is running.

Syntax 

CURRENT_DATE

CURRENT_TIME

This function returns the current time. The time is determined by the system in which the Oracle BI Server is running.

Syntax 

CURRENT_TIME(integer)

Where:

integer is any integer representing the number of digits of precision with which to display the fractional second. The argument is optional; the function returns the default precision when no argument is specified.

CURRENT_TIMESTAMP

This function returns the current date/timestamp. The timestamp is determined by the system in which the Oracle BI Server is running.

Syntax 

CURRENT_TIMESTAMP(integer)

Where:

integer is any integer representing the number of digits of precision with which to display the fractional second. The argument is optional; the function returns the default precision when no argument is specified.

DAY_OF_QUARTER

This function returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date.

Syntax 

DAY_OF_QUARTER(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

DAYNAME

This function returns the name of the day of the week for a specified date.

Syntax 

DAYNAME(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

DAYOFMONTH

This function returns the number corresponding to the day of the month for a specified date.

Syntax 

DAYOFMONTH(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

DAYOFWEEK

This function returns a number between 1 and 7 corresponding to the day of the week, Sunday through Saturday, for a specified date. For example, the number 1 corresponds to Sunday, and the number 7 corresponds to Saturday.

Syntax 

DAYOFWEEK(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

DAYOFYEAR

This function returns the number (between 1 and 366) corresponding to the day of the year for a specified date.

Syntax 

DAYOFYEAR(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

HOUR

This function returns a number (between 0 and 23) corresponding to the hour for a specified time. For example, 0 corresponds to 12 a.m. and 23 corresponds to 11 p.m.

Syntax 

HOUR(timeExpr)

Where:

timeExpr is any expression that evaluates to a time.

MINUTE

This function returns a number (between 0 and 59) corresponding to the minute for a specified time.

Syntax 

MINUTE(timeExpr)

Where:

timeExpr is any expression that evaluates to a time.

MONTH

This function returns the number (between 1 and 12) corresponding to the month for a specified date.

Syntax 

MONTH(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

MONTH_OF_QUARTER

This function returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date.

Syntax 

MONTH_OF_QUARTER(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

MONTHNAME

This function returns the name of the month for a specified date.

Syntax 

MONTHNAME(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

NOW

This function returns the current timestamp. The NOW function is equivalent to the CURRENT_TIMESTAMP function.

Syntax 

NOW()

QUARTER_OF_YEAR

This function returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date.

Syntax 

QUARTER_OF_YEAR(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

SECOND

This function returns the number (between 0 and 59) corresponding to the seconds for a specified time.

Syntax 

SECOND(timeExpr)

Where:

timeExpr is any expression that evaluates to a time.

TIMESTAMPADD

This function adds a specified number of intervals to a specified timestamp, and returns a single timestamp.

In the simplest scenario, this function adds the specified integer value to the appropriate component of the timestamp, based on the interval. Adding a week translates to adding seven days, and adding a quarter translates to adding three months. A negative integer value results in a subtraction (such as going back in time).

An overflow of the specified component (such as more than 60 seconds, 24 hours, 12 months, and so on) necessitates adding an appropriate amount to the next component. For example, when adding to the day component of a timestamp, this function considers overflow and takes into account the number of days in a particular month (including leap years when February has 29 days).

When adding to the month component of a timestamp, this function verifies that the resulting timestamp has enough days for the day component. For example, adding 1 month to 2000-05-31 does not result in 2000-06-31 because June does not have 31 days. This function reduces the day component to the last day of the month, 2000-06-30 in this example.

A similar issue arises when adding to the year component of a timestamp having a month component of February and a day component of 29 (that is, last day of February in a leap year). If the resulting timestamp does not fall on a leap year, the function reduces the day component to 28.

These actions conform to the behavior of Microsoft SQL Server and the native OCI interface for Oracle Database.

Syntax 

TIMESTAMPADD(interval, intExpr, timestamp)

Where:

interval is the specified interval. Valid values are:

• SQL_TSI_SECOND

• SQL_TSI_MINUTE

• SQL_TSI_HOUR

• SQL_TSI_DAY

• SQL_TSI_WEEK

• SQL_TSI_MONTH

• SQL_TSI_QUARTER

• SQL_TSI_YEAR

intExpr is any expression that evaluates to an integer value.

timestamp is any valid timestamp. This value is used as the base in the calculation.

A null integer expression or a null timestamp passed to this function results in a null return value.

Examples 

The following query asks for the resulting timestamp when 3 days are added to 2000-02-27 14:30:00. Since February, 2000 is a leap year, the query returns a single timestamp of 2000-03-01 14:30:00.

SELECT TIMESTAMPADD(SQL_TSI_DAY, 3, TIMESTAMP'2000-02-27 14:30:00')
FROM Employee WHERE employeeid = 2;

The following query asks for the resulting timestamp when 7 months are added to 1999-07-31 0:0:0. The query returns a single timestamp of 2000-02-29 00:00:00. Notice the reduction of day component to 29 because of the shorter month of February.

SELECT TIMESTAMPADD(SQL_TSI_MONTH, 7, TIMESTAMP'1999-07-31 00:00:00')
FROM Employee WHERE employeeid = 2;

The following query asks for the resulting timestamp when 25 minutes are added to 2000-07-31 23:35:00. The query returns a single timestamp of 2000-08-01 00:00:00. Notice the propagation of overflow through the month component.

SELECT TIMESTAMPADD(SQL_TSI_MINUTE, 25, TIMESTAMP'2000-07-31 23:35:00')
FROM Employee WHERE employeeid = 2;

TIMESTAMPDIFF

This function returns the total number of specified intervals between two timestamps.

This function first determines the timestamp component that corresponds to the specified interval parameter, and then looks at the higher order components of both timestamps to calculate the total number of intervals for each timestamp. For example, if the specified interval corresponds to the month component, the function calculates the total number of months for each timestamp by adding the month component and twelve times the year component. Then the function subtracts the first timestamp's total number of intervals from the second timestamp's total number of intervals.

The TIMESTAMPDIFF function rounds up to the next integer whenever fractional intervals represent a crossing of an interval boundary. For example, the difference in years between 1999-12-31 and 2000-01-01 is one year because the fractional year represents a crossing from one year to the next (such as 1999 to 2000). By contrast, the difference between 1999-01-01 and 1999-12-31 is zero years because the fractional interval falls entirely within a particular year (that is, 1999). Microsoft SQL Server exhibits the same rounding behavior, but IBM DB2 does not; it always rounds down.

When calculating the difference in weeks, the function calculates the difference in days and divides by seven before rounding. Additionally, the function takes into account how the parameter FIRST_DAY_OF_THE_WEEK has been configured in the NQSConfig.INI file. For example, with Sunday as the start of the week, the difference in weeks between 2000-07-06 (a Thursday) and 2000-07-10 (the following Monday) results in a value of 1 week. With Tuesday as the start of the week, however, the function would return zero weeks since the fractional interval falls entirely within a particular week. When calculating the difference in quarters, the function calculates the difference in months and divides by three before rounding.

The Oracle BI Server pushes down the TIMESTAMPADD and TIMESTAMPDIFF functions to Microsoft SQL Server, Oracle Database, IBM DB2, and ODBC databases by default.

Syntax 

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

Where:

interval is the specified interval. Valid values are:

• SQL_TSI_SECOND

• SQL_TSI_MINUTE

• SQL_TSI_HOUR

• SQL_TSI_DAY

• SQL_TSI_WEEK

• SQL_TSI_MONTH

• SQL_TSI_QUARTER

• SQL_TSI_YEAR

timestamp1 and timestamp2 are any valid timestamps.

A null timestamp parameter passed to this function results in a null return value.

Example 

The following example query asks for a difference in days between timestamps 1998-07-31 23:35:00 and 2000-04-01 14:24:00. It returns a value of 610. Notice that the leap year in 2000 results in an additional day.

SELECT TIMESTAMPDIFF
(SQL_TSI_DAY, TIMESTAMP'1998-07-31 23:35:00',TIMESTAMP'2000-04-01 14:24:00')
FROM Employee WHERE employeeid = 2;

WEEK_OF_QUARTER

This function returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date.

Syntax 

WEEK_OF_QUARTER(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

WEEK_OF_YEAR

This function returns a number (between 1 and 53) corresponding to the week of the year for the specified date.

Syntax 

WEEK_OF_YEAR(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

YEAR

This function returns the year for the specified date.

Syntax 

YEAR(dateExpr)

Where:

dateExpr is any expression that evaluates to a date.

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

EVALUATE

This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for scalar calculations, and is useful when you want to use a specialized database function that is not supported by the Oracle BI Server, but that is understood by the underlying data source.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Syntax 

EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])

Where:

db_function is any valid database function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Examples 

This example shows an embedded database function.

SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees

Examples Using EVALUATE_AGGREGATE and EVALUATE to Leverage Unique Essbase Functions

The following examples use the EVALUATE_AGGREGATE and EVALUATE functions. Note that expressions are applied to columns in the logical table source that refers to the physical cube.Use EVALUATE_AGGREGATE to implement custom aggregations. For example, you may want to compare overall regional profit to profits for the top three products in the region. You can define a new measure to represent the profits for top three products resulting in the Logical SQL statement:

SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2), %3)',
Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic

The Oracle BI Server generates the following expression for the custom aggregation:

member [Measures].[MS1] AS 'SUM(Topcount([Product].Generations(6).members,3,[Measures].[Profit]),[Measures].[Profit])'

Use the EVALUATE function on projected dimensions to implement scalar functions that are computed post-aggregation. EVALUATE may change the grain of the query, if its definition makes explicit references to dimensions (or attributes) that are not in the query.

For example, if you would like to see the Profits for the top five products ranked by Sales sold in a Region, after creating the applicable measure, the resulting Logical SQL statement is as follows

SELECT Region, EVALUATE('TopCount(%1.members, 5, %2)' as VARCHAR(20), Products, Sales), Profits FROM SampleBasic

The Oracle BI Server generates the following expression to retrieve the top five products:

set [Evaluate0] as '{Topcount([Product].Generations(6).members,5,[Measures].[Sales]) }'

EVALUATE_ANALYTIC

This function passes the specified database analytic function with optional referenced columns as parameters to the back-end data source for evaluation.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Syntax 

EVALUATE_ANALYTIC('db_function(%1...%N)' [AS data_type] [, column1, columnN])

Where:

db_function is any valid database analytic function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific analytic function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Examples 

This example shows an embedded database analytic function.

EVALUATE_ANALYTIC('dense_rank() over(order by %1 )' AS INT,sales.revenue)

If the preceding example needs to return a double, then an explicit cast should be added, as follows:

CAST(EVALUATE_ANALYTIC('Rank(%1.dimension.currentmember, %2.members)',
"Foodmart93"."Time"."Month" as Double)

EVALUATE_AGGR

This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for aggregate functions with a GROUP BY clause.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Syntax 

EVALUATE_AGGR('db_agg_function(%1...%N)' [AS data_type] [, column1, columnN)

Where:

db_agg_function is any valid aggregate database function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Example 

EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)

EVALUATE_PREDICATE

This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for functions with a return type of Boolean.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Note that EVALUATE_PREDICATE is not supported for use with Essbase data sources.

Syntax 

EVALUATE_PREDICATE('db_function(%1...%N)', [, column1, columnN)

Where:

db_function is any valid database function with a return type of Boolean that is understood by the underlying data source.

column1 through columnN is an optional, comma-delimited list of columns.

If you want to model a database function for comparison purposes, you should not use EVALUATE_PREDICATE. Instead, use EVALUATE and put the comparison outside the function. For example, do not use EVALUATE_PREDICATE as follows:

EVALUATE_PREDICATE('dense_rank() over (order by 1% ) < 5', sales.revenue)

Instead, use EVALUATE, as follows:

EVALUATE('dense_rank() over (order by 1% ) ', sales.revenue) < 5

Example 

SELECT year, Sales AS DOUBLE,CAST(EVALUATE('OLAP_EXPRESSION(%1,''LAG(units_cube_
sales, 1, time, time LEVELREL time_levelrel)'')', OLAP_CALC) AS DOUBLE) FROM 
"Global".Time, "Global"."Facts - sales" WHERE EVALUATE_PREDICATE('OLAP_
CONDITION(%1, ''LIMIT time KEEP ''''1'''', ''''2'''', ''''3'''', ''''4'''' '') 
=1', OLAP_CALC) ORDER BY year;

USER

This function returns the user name for the Oracle BI repository to which you are logged on.

Syntax 

USER()

DATABASE

This function returns the name of the default subject area.

Syntax 

DATABASE()