This appendix provides syntax and usage information for the Logical SQL statements understood by the Oracle BI Server. Oracle BI Server Logical SQL includes standard SQL, plus special functions (SQL extensions) such as AGO
, TODATE
, EVALUATE
, and others. Logical SQL queries resolve to Presentation layer objects.
This appendix contains the following topics:
The Oracle BI Server accepts SQL SELECT
statements from client tools. Additionally, the Oracle BI Administration Tool enables you to define logical columns with complex expressions. This appendix explains the syntax and semantics for the SELECT
statement and for the expressions you can use in the Administration Tool to create derived columns.
The abstraction provided by the Presentation layer and Business Model and Mapping layer enables clients to query data with Logical SQL only, so that the interaction with actual physical sources is handled by the Oracle BI Server. The complexity of the multiple source languages needed to communicate with each data source type is hidden from users and clients.
In Answers, you can view the Logical SQL queries issued by Oracle BI Presentation Services for particular analyses by viewing the SQL Issued section of the Advanced tab of the Analysis editor. If you have the appropriate privileges, then you can also view SQL by displaying the Manage Sessions page in the Administration tab. Click View Log from the Manage Sessions page to see further details.
In Answers, there are also several places where you can issue Logical SQL. If you have the appropriate privileges, then you can use the Issue SQL page in the Administration tab to enter any SQL code to send to the Oracle BI Server. If an analysis does not contain hierarchical columns, member selections, or groups, then you can use the Advanced SQL Clauses fields in the Advanced tab of the Analysis editor. You can also enter SQL in the New Filter dialog.
Other clients, like Oracle BI Publisher, Oracle's Hyperion Interactive Reporting, the Oracle BI Add-in for Microsoft Office, and Essbase, also provide their own interfaces to view and issue Logical SQL to the Oracle BI Server.
This section explains SQL syntax and semantics. The following topics are included:
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:
Syntax for the SELECT
statement is as follows:
SELECT [DISTINCT] select_list FROM from_clause [WHERE search_condition] [GROUP BY column {, column} [HAVING search_condition]] [ORDER BY column {, column}]
Where:
select_list
is the list of columns specified in the request. See "SELECT List Syntax" for more information.
FROM
from_clause
is the list of tables in the request. Optionally includes certain join information for the request. See "FROM Clause Syntax" for more information.
WHERE
search_condition
specifies any combination of conditions to form a conditional test. A WHERE
clause acts as a filter that lets you constrain a request to obtain results that answer a particular question. Together with the columns you select, filters determine what your results will contain. See "WHERE Clause Syntax" for more information.
GROUP BY
column
{,
column
}
specifies a column (or alias) belonging to a table defined in the data source. See "GROUP BY Clause Syntax" for more information.
HAVING
search_condition
specifies any combination of conditions to form a conditional test. The syntax is identical to that for the WHERE
clause.
ORDER BY
column
{,
column
}
specifies the columns to order the results by. See "ORDER BY Clause Syntax" for more information.
The Oracle BI Server treats the SELECT
statement as a logical request. If aggregated data is requested in the SELECT
statement, a GROUP BY
clause is automatically assumed by the server. Any join conditions supplied in the query are ignored because the join conditions are all predefined in the Oracle BI repository.
The Oracle BI Server accepts the following SQL syntaxes for comments:
/* */ C-style comments
// Double slash for single-line comments
# Number sign for single-line comments
The Oracle BI Server supports certain subqueries, as well as UNION
, UNION ALL
, INTERSECT
, and EXCEPT
operations in logical requests. This functionality increases the range of business questions that can be answered, eases the formulation of queries, and provides some ability to query across multiple business models.
The Oracle BI Server supports the following subquery predicates in any conditional expression (for example, within WHERE
, HAVING
, or CASE
statements):
IN, NOT IN Any, >=Any, =Any, <Any, <=Any, <>Any All, >=All, =All, <All,<=All, <>All EXISTS, NOT EXISTS
In Answers, advanced users and developers can use the Advanced SQL Clauses fields in the Advanced tab of the Analysis editor to specify various SQL clauses, such as GROUP BY
, HAVING
, and DISTINCT
, to include in the SQL queries that are sent to the Oracle BI Server. If an analysis contains hierarchical columns, selections, or groups, then certain Advanced SQL Clauses fields are not available.
The select_list
lists the columns in the request. All columns need to be from a single business model. Table names can be included (as Table.Column), but are optional unless column names are not unique within a business model. If column names contain spaces, enclose column names in double quotes. The DISTINCT
keyword does not need to be included, because the Oracle BI Server always does a distinct query. Columns that are being aggregated do not need to include the aggregation function (such as SUM
), as aggregation rules are known to the server and aggregation is performed automatically.
... * | (column | expr) [[AS] alias] {, (column | expr) [[AS] alias] } ...
Where:
*
Indicates all columns in the resultant table in the FROM
clause.
column
is a column (or alias) belonging to a table defined in the data source.
expr
is any valid SQL expression.
Note: You cannot use * to select all columns from the Advanced tab of the Analysis editor in Answers. Instead, you must specify particular columns.
The Oracle BI Server accepts any valid SQL FROM
clause syntax. To simplify FROM
clause creation, you can specify the name of a subject area instead of a list of tables. The Oracle BI Server determines the proper tables and the proper join specifications based on the columns the request asks for and the configuration of the Oracle BI repository.
The Oracle BI Server accepts any valid SQL WHERE
clause syntax. There is no need to specify any join conditions in the WHERE
clause, because the joins are all configured within the Oracle BI repository. Any join conditions specified in the WHERE
clause are ignored.
The Oracle BI Server also supports the following subquery predicates in any conditional expression (WHERE
, HAVING
or CASE
statements):
IN, NOT IN Any, >=Any, =Any, <Any, <=Any, <>Any All, >=All, =All, <All,<=All, <>All EXISTS, NOT EXISTS
With auto aggregation on the Oracle BI Server, there is no need to submit a GROUP BY
clause. When no GROUP BY
clause is specified, the GROUP BY
specification defaults to all of the nonaggregation columns in the SELECT
list. If you explicitly use aggregation functions in the select list, you can specify a GROUP BY
clause with different columns and the Oracle BI Server computes the results based on the level specified in the GROUP BY
clause.
For an in-depth explanation and some examples of using the GROUP BY
clause in requests against the Oracle BI Server, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
The Oracle BI Server accepts any valid SQL ORDER BY
clause syntax, including referencing columns by their order in the select list (such as ORDER BY
3, 1, 5).
In addition, you can use the following syntax to alter the sort order for nulls in the query:
ORDER BY col1 NULLS LAST, ORDER BY col2 NULLS FIRST
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
Note:
SELECT_PHYSICAL statements are not cached.
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:
Basic syntax for SELECT_PHYSICAL
queries is equivalent to "Basic Syntax for the SELECT Statement" with the term SELECT_PHYSICAL
replacing the word SELECT
, namely:
SELECT_PHYSICAL [DISTINCT] select_list FROM from_clause [WHERE search_condition] [GROUP BY column {, column} [HAVING search_condition]] [ORDER BY column {, column}]
Notes:
The SELECT_PHYSICAL
statement is close to the standard ANSI SQL SELECT
statement. For example, you cannot omit the GROUP BY
clause nor, where relevant, the HAVING
clause in a SELECT_PHYSICAL
aggregate query.
In SELECT_PHYSICAL
queries, you must fully qualify the table names in the FROM
list. Each fully qualified table name must match a table name in the physical layer of the repository.
A fully qualified table name consists of up to four components, database name, catalog name, schema name, and table name. Each component is surrounded by double quotes (") with a period (.) separator between components. For example, "SQL_DB"."My_Catalog"."My_Schema"."Customers" for a SQL Server table, and "FoodMart"..."Sales" for a cube table.
Refer to the corresponding topics in "Basic Syntax for the SELECT Statement" for more information about the different clauses and sub-clauses of the SELECT_PHYSICAL
command.
The following aggregate functions are not supported in SELECT_PHYSICAL
queries:
AGO
BOTTOMN
FILTER
FIRST
LAST
RCOUNT
RMAX
RMIN
RSUM
TODATE
TOPN
The Oracle BI Server supports the use of SELECT_PHYSICAL
for the following types of logical query:
Standard Non-Aggregate Queries
Standard non-aggregate SELECT_PHYSICAL
commands follow the same rules as standard non-aggregate SELECT
commands. They can also include scalar functions, such as String, Math, and Calendar Date/Time functions. For example:
SELECT_PHYSICAL productid, categoryid FROM "My_DB"."My_Schema"."products" WHERE categoryid > 5;
SELECT_PHYSICAL LEFT(productname,10) FROM "My_DB"."My_Schema"."products" WHERE productname is not null;
Queries with Aggregate Functions
In general, all aggregate functions supported in SELECT
queries are also supported in SELECT_PHYSICAL
queries. See "Aggregate Functions Not Supported in SELECT_PHYSICAL Queries" for a list of the exceptions to this rule.
For aggregates supported in SELECT_PHYSICAL
commands, each aggregate must have an explicitly specified aggregation level, using the GROUP
BY
clause or the BY
clause. For example:
SELECT_PHYSICAL employeeid, SUM(quantity by) FROM "My_DB"."My_Schema"."employees"; SELECT_PHYSICAL employeeid, SUM(quantity) FROM "My_DB"."My_Schema"."employees" GROUP BY employeeid HAVING SUM(quantity) > 100;
Subqueries
The Oracle BI Server supports the following types of query:
Queries where both the parent query and the subquery use SELECT_PHYSICAL
Parent query uses SELECT
and subquery uses SELECT_PHYSICAL
Subqueries are supported on both filters and on projections embedded in a Case statement.
For example:
SELECT_PHYSICAL * FROM "My_DB"."My_Schema"."products" WHERE supplierid IN (SELECT_PHYSICAL supplierid FROM "My_DB"."My_Schema"."suppliers"); SELECT productid FROM snowflakesales.product WHERE categoryid IN (SELECT_PHYSICAL categoryid FROM "My_DB"."My_Schema"."categories");
SELECT CASE WHEN b.categoryid IN (SELECT_PHYSICAL a.categoryid FROM "My_DB"."My_Schema"."products" a) THEN b.categoryid END FROM categories b;
Queries with Derived Tables
Both SELECT
and SELECT_PHYSICAL
queries can have derived tables in their FROM
clause. The tables can be derived using either SELECT
or SELECT_PHYSICAL
. For example:
SELECT_PHYSICAL COUNT(DISTINCT t.rto) FROM (SELECT_PHYSICAL employeeid AS id, reportsto AS rto FROM "My_DB"."My_Schema"."employees") t;
SELECT productid, categoryid FROM (SELECT_PHYSICAL productid, categoryid FROM "My_DB"."My_Schema"."products" a LEFT OUTER JOIN "My_DB"."My_Schema"."categories" b ON a.categoryid = b.categoryid);
SELECT y.cid, sum(x.qty) FROM (SELECT productid pid, categoryid cid, qtysold qty FROM sales.product) x RIGHT OUTER JOIN (SELECT_PHYSICAL CASE categoryid WHEN 1 THEN null ELSE categoryid END cid FROM "My_DB"."My_Schema"."categories") y ON x.cid = y.cid GROUP BY y.cid;
Cross-Database Queries
You can use SELECT_PHYSICAL
to join tables in different databases. For example:
SELECT_PHYSICAL a.productid, b.categoryid FROM "My_DB"."My_Schema"."products" a FULL OUTER JOIN "My_DB2"."My_Schema"."categories" b ON a.categoryid = b.categoryid
SELECT_PHYSICAL
queries support the NATURAL JOIN
syntax, which enables you to use predefined join expressions. For ADF data sources, the ViewLink in ADF becomes active. The NATURAL JOIN
join type, however, is not exposed for use in Logical Table Sources (for example, LEFT OUTER JOIN
).
You can only use the NATURAL JOIN
keyword in SELECT_PHYSICAL
queries. The NATURAL JOIN
behavior in Oracle Business Intelligence is different from the ANSI NATURAL JOIN
. The following examples illustrate how joins are executed with and without the NATURAL JOIN
syntax:
SELECT PHYSICAL * FROM A, B;
In this example, no join is executed between A and B (even if one is defined in the metadata).
SELECT_PHYSICAL * FROM A NATURAL JOIN B;
In this example, the physical join between A and B is executed. For ADF data sources, the join expression defined by the underlying ViewLink is used.
SELECT_PHYSICAL * FROM C, A NATURAL JOIN B;
In this example, even if C is joined to A in the metadata, only the A-B join is active. The C-A join is not used.
You can use session variables and the INDEXCOL
function in a SELECT_PHYSICAL
command, as in the following examples:
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";
You can use the FETCH
and OFFSET
clauses to constrain the number of rows returned by the SELECT
statement and to skip a specified number of rows from the beginning of the result set. Both clauses are optional and can be used together, or independently. The fetch and offset clauses are part of the SELECT
statement and are placed at the end.
These clauses are useful for situations where you have a large result set (such as with a large dimension), and you want to present, for example, the first 100 rows to the user. The Oracle BI Server stops processing when the limit is reached, improving overall performance and conserving resources. In addition, the limit is pushed to the back-end database in many cases so that the database can optimize the query.
Technically, both clauses can be used without an ORDER BY
clause, but the results would be non-deterministic. Because of this, both clauses should always be used with ORDER BY
.
If OFFSET
is not specified, the default value is 0, which means that results are returned starting from the first row. If FETCH
is not specified, it means that there is no limitation on the number rows returned.
Both clauses are evaluated after the WHERE
clause, aggregation, HAVING
clause, window analytic function, and ORDER BY
clause. Both clauses can be used with SELECT_PHYSICAL
in addition to SELECT
.
OFFSET n ROW[S]
n
is the number of rows you want to skip from the beginning of the result set. Note that n
must be greater than zero.
FETCH FIRST | NEXT n ROW[S] ONLY
n is the number of rows you want to retrieve. Note that n must be greater than zero.
Typically, FIRST
is used when the limit clause is used independently of the offset clause, while NEXT
is used when the limit clause is used in conjunction with the offset clause.
SELECT employeeid, firstname, revenue FROM sales.employee ORDER BY revenue desc OFFSET 2 ROWS FETCH NEXT 4 ROWS ONLY
The following table lists the entire result set without the OFFSET
and FETCH
clauses. When the OFFSET
and FETCH
clauses are included, only the rows shown in bold are returned.
Employeeid | FirstName | Revenue |
---|---|---|
4 |
Margaret |
250187.45 |
3 |
Janet |
213051.30 |
1 |
Nancy |
202143.71 |
2 |
Andrew |
202143.71 |
7 |
Robert |
177749.26 |
8 |
Laura |
141295.99 |
9 |
Anne |
133301.03 |
6 |
Michael |
82964.00 |
5 |
Steven |
78198.10 |
There are two types of operators: SQL logical operators, and mathematical operators.
The following SQL logical operators are used to specify comparisons between expressions.
Between: Used to determine boundaries for a condition. Each boundary is an expression, and the bounds do not include the boundary limits, as in less than and greater than (as opposed to less than or equal to and greater than or equal to). BETWEEN
can be preceded with NOT
to negate the condition.
In: Specifies a comparison of a column value with a set of values.
Is Null: Specifies a comparison of a column value with the null value.
Like: Specifies a comparison to a literal value. Often used with wildcard characters to indicate any character string match of zero or more characters (%) or a any single character match (_).
Mathematical operators are used to combine expression elements to make certain types of comparisons in an expression.
Table D-1 lists operators and describes their use in an expression.
Operator | Description |
---|---|
+ |
Plus sign for addition. |
- |
Minus sign for subtraction. |
* |
Multiply sign for multiplication. |
/ |
Divide by sign for division. |
|| |
Character string concatenation. |
( |
Open parenthesis. |
) |
Closed parenthesis. |
> |
Greater than sign, indicating values higher than the comparison. |
< |
Less than sign, indicating values lower than the comparison. |
= |
Equal sign, indicating the same value. |
<= |
Less than or equal to sign, indicating values the same or lower than the comparison. |
>= |
Greater than or equal to sign, indicating values the same or higher than the comparison. |
<> |
Not equal to, indicating values higher or lower, but different. |
|
|
|
|
|
|
, |
Comma, used to separate elements in a list. |
Expressions are building blocks for creating conditional expressions that convert a value from one form to another. Expressions include:
This form of the CASE
statement is also referred to as the CASE(Lookup)
form. The value of expr1
is examined, then the WHEN
expressions. If expr1
matches any WHEN
expression, it assigns the value in the corresponding THEN
expression.
If none of the WHEN
expressions match, it assigns the default value specified in the ELSE
expression. If no ELSE
expression is specified, the system automatically adds an ELSE NULL
.
If expr1
matches an expression in multiple WHEN
clauses, only the expression following the first match is assigned.
Note:
In a CASE
statement, AND
has precedence over OR
.
CASE expr1 WHEN expr2 THEN expr3 {WHEN expr... THEN expr...} ELSE expr END
Where:
CASE
starts the CASE
statement. Must be followed by an expression and one or more WHEN
and THEN
statements, an optional ELSE
statement, and the END
keyword.
WHEN
specifies the condition to be satisfied.
THEN
specifies the value to assign if the corresponding WHEN
expression is satisfied.
ELSE
specifies the value to assign if none of the WHEN
conditions are satisfied. If omitted, ELSE NULL
is assumed.
END
ends the CASE
statement.
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
In this example, the WHEN
statements must reflect a strict equality. For example, a WHEN
condition of WHEN < 0 THEN 'Under Par'
is illegal because comparison operators are not allowed.
This form of the CASE
statement evaluates each WHEN
condition and if satisfied, assigns the value in the corresponding THEN
expression.
If none of the WHEN
conditions are satisfied, it assigns the default value specified in the ELSE
expression. If no ELSE
expression is specified, the system automatically adds an ELSE NULL
.
Note:
In a CASE
statement, AND
has precedence over OR
.
CASE WHEN request_condition1 THEN expr1 {WHEN request_condition2 THEN expr2} {WHEN request_condition... THEN expr...} ELSE expr END
Where:
CASE
starts the CASE
statement. Must be followed by one or more WHEN
and THEN
statements, an optional ELSE
statement, and the END
keyword.
WHEN
specifies the condition to be satisfied.
THEN
specifies the value to assign if the corresponding WHEN
expression is satisfied.
ELSE
specifies the value to assign if none of the WHEN
conditions are satisfied. If omitted, ELSE NULL
is assumed.
END
ends the CASE
statement.
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
Unlike the Switch form of the CASE
statement, the WHEN
statements in the If form allow comparison operators. For example, a WHEN
condition of WHEN < 0 THEN 'Under Par'
is legal.
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:
A character literal represents a value of CHARACTER
or VARCHAR
data type. To express a character literal, enclose the character string in single quotes ( ' ). The number of characters enclosed between the single quotes implies the length of the literal.
'Oracle BI Server' 'abc123'
The SQL 92 standard defines three kinds of 'typed' datetime literals, in the following formats:
DATE 'yyyy-mm-dd' TIME 'hh:mm:ss' TIMESTAMP 'yyyy-mm-dd hh:mm:ss'
To express a typed datetime literal, use the keywords DATE
, TIME
, or TIMESTAMP
followed by a datetime string enclosed in single quotation marks, as in the preceding example. Two digits are required for all nonyear components even if the value is a single digit.
DATE '2000-08-15' TIME '11:55:25' TIMESTAMP '1999-03-15 11:55:25'
A numeric literal represents a value of a numeric data type (such as INTEGER
, DECIMAL
, or FLOAT
). To express a numeric literal, type the number as part of a SQL statement.
Do not surround numeric literals with single quotes. Doing so expresses the literal as a character literal.
Note:
When ENABLE_NUMERIC_DATA_TYPE
is set to YES
in NQSConfig.INI, all decimal literals (or integer literals that are too large to fit in the INT data type) are parsed internally within the Oracle BI Server as NUMERIC.
When treating literals as NUMERIC, be aware of the Oracle standard double promotion rules, including the following:
DOUBLE/NUMBER = DOUBLE , DOUBLE * NUMBER = DOUBLE
Because the parsing of numeric literals happens very early in the query processing before the actual data source is known, internally, the Oracle BI Server treats decimal numbers as NUMERIC if ENABLE_NUMERIC_DATA_TYPE
is set to YES
, regardless of data source type.
When NUMERIC is enabled and the Oracle BI Server executes an expression internally involving decimal literals, the server treats the literals as NUMERIC even if the back-end data source does not support the NUMERIC data type. However, the type promotion rules still apply. For example, if the Oracle BI Server retrieves the data from a data source as DOUBLE and combines that with a NUMERIC literal during internal execution, the final result is still be converted to DOUBLE.
Numeric literals include:
To express an integer constant as a literal, specify the integer as part of a SQL statement (for example, in the SELECT
list). Precede the integer with a plus sign (+) to indicate the integer is positive, or a minus sign (-) to indicate the integer is negative. Unsigned integers are assumed to be positive.
234 +2 567934
To express a decimal literal, specify a decimal number. Precede the number with a plus sign (+) to indicate the number is positive, or a minus sign (-) to indicate the number is negative. Unsigned numbers are assumed to be positive.
1.223 -22.456 +33.456789
To express floating point numbers as literal constants, enter a decimal literal followed by the letter E (either uppercase or lowercase), followed by the plus sign (+) to indicate a positive exponent, or the minus sign (-) to indicate a negative exponent. No spaces are allowed between the integer, the letter E, and the sign of the exponent.
333.456E- 1.23e+
You can include and set variables in SQL statements. To do this, include the variable at the beginning of the SQL statement.
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.
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"
This section contains information about aggregate functions, running aggregate functions, and time series functions:
Aggregate functions perform operations on multiple values to create summary results.
Aggregate functions include:
This function aggregates columns based on the level or levels you specify. Using AGGREGATE AT
guarantees that the aggregate for the measure always occurs at the levels specified after the keyword AT
, regardless of the WHERE
clause.
AGGREGATE(expr AT level [, level1, levelN])
Where:
expr
is any expression that references at least one measure column
level
is the level at which you want to aggregate. You can optionally specify multiple levels.
You cannot specify a level from a dimension that contains levels that are being used as the measure level for the measure you specified in the first argument. For example, you cannot write the function as AGGREGATE(yearly_sales AT month)
because "month" is from the same time dimension that is being used as the measure level for "yearly_sales."
The following example shows the AGGREGATE AT
function and example results:
SELECT month, year, AGGREGATE(sales AT Year)FROM timeseriestestingWHERE year = 1994 AND month = 12
Result:
Month Year AGGREGATE AT year12 1994 7396Row count: 1
Because the AGGREGATE AT
operator is always executed before the predicates, it always returns the correct total for the time level specified after the keyword AT
.
This function calculates the average (mean) value of an expression in a result set. It must take a numeric expression as its argument.
Note that the denominator of AVG
is the number of rows aggregated. For this reason, it is usually a mistake to use AVG(
x
)
in a calculation in Oracle Business Intelligence. Instead, write the expression manually so that you can control both the numerator and denominator (x/y).
AVG(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the average (mean) of all distinct values of an expression. It must take a numeric expression as its argument.
AVG(DISTINCT numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numeric value. The BOTTOMN
function operates on the values returned in the result set. A request can contain only one BOTTOMN
expression.
BOTTOMN(numExpr, integer)
Where:
numExpr
is any expression that evaluates to a numeric value.
integer
is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.
This function calculates the number of rows having a nonnull value for the expression. The expression is typically a column name, in which case the number of rows with nonnull values for that column is returned.
Syntax:
COUNT(expr)
Where:
expr
is any expression.
This function adds distinct processing to the COUNT
function.
COUNT(DISTINCT expr)
Where:
expr
is any expression.
This function counts the number of rows.
COUNT(*)
For example, if a table named Facts contained 200,000,000 rows, the sample request would return the results shown:
SELECT COUNT(*) FROM Facts
Result:
200000000
This function calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.
MAX(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the median (middle) value of the rows satisfying the numeric expression argument. When there are an even number of rows, the median is the mean of the two middle rows. This function always returns a double.
MEDIAN(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.
MIN(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. In other words, the resulting sorted data set is broken into several tiles where there are roughly an equal number of values in each tile.
NTile
with numTiles
= 100 returns what is commonly called the "percentile" (with numbers ranging from 1 to 100, with 100 representing the high end of the sort). This value is different from the results of the Oracle BI PERCENTILE
function, which conforms to what is called "percent rank" in SQL 92 and returns values from 0 to 1.
NTILE(numExpr, numTiles)
Where:
numExpr
is any expression that evaluates to a numeric value.
numTiles
is a positive, nonnull integer that represents the number of tiles.
If the numExpr
argument is not null, the function returns an integer that represents a rank within the requested range.
This function calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.
The percentile is calculated based on the values in the result set.
PERCENTILE(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).
The rank is calculated based on the values in the result set.
RANK(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function returns the standard deviation for a set of values. The return type is always a double. STDEV_SAMP
is a synonym for STDDEV
.
STDDEV([ALL | DISTINCT] numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
If ALL
is specified, the standard deviation is calculated for all data in the set.
If DISTINCT
is specified, all duplicates are ignored in the calculation.
If nothing is specified (the default), all data is considered.
This function returns the standard deviation for a set of values using the computational formula for population variance and standard deviation.
STDDEV_POP([ALL | DISTINCT] numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
If ALL
is specified, the standard deviation is calculated for all data in the set.
If DISTINCT
is specified, all duplicates are ignored in the calculation.
If nothing is specified (the default), all data is considered.
This function calculates the sum obtained by adding up all values satisfying the numeric expression argument.
SUM(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the sum obtained by adding all of the distinct values satisfying the numeric expression argument.
SUM(DISTINCT numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numeric value. The TOPN
function operates on the values returned in the result set. A request can contain only one TOPN
expression.
TOPN(numExpr, integer)
Where:
numExpr
is any expression that evaluates to a numeric value.
integer
is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.
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:
This function calculates a moving average (mean) for the last n rows of data in the result set, inclusive of the current row.
The average for the first row is equal to the numeric expression for the first row. The average for the second row is calculated by taking the average of the first two rows of data. The average for the third row is calculated by taking the average of the first three rows of data, and so on until you reach the nth row, where the average is calculated based on the last n rows of data.
MAVG(numExpr, integer)
Where:
numExpr
is any expression that evaluates to a numeric value.
integer
is any positive integer. Represents the average of the last n rows of data.
This function calculates a moving sum for the last n rows of data, inclusive of the current row.
The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data. The sum for the third row is calculated by taking the sum of the first three rows of data, and so on. When the nth row is reached, the sum is calculated based on the last n rows of data.
MSUM(numExpr, integer)
Where:
numExpr
is any expression that evaluates to a numeric value.
integer
is any positive integer. Represents the average of the last n rows of data.
This example shows a query that uses the MSUM
function, along with example query results.
select month, revenue, MSUM(revenue, 3) as 3_MO_SUM from sales_subject_area
Result:
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
This function calculates a running sum based on records encountered so far. The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data. The sum for the third row is calculated by taking the sum of the first three rows of data, and so on.
RSUM(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
In Answers, you can also use the following alternate syntax:
RSUM(expression1 [BY expression2[, expression3[, ...]]])
Where:
expression1, expression2, expression3 ...
can be any column reference, or an arithmetic expression on column references.
The BY
clause causes the RSUM
computation to restart at the row where the value of any of the BY
columns differs from the previous row.
This example shows a query that uses the RSUM
function, along with example query results.
SELECT month, revenue, RSUM(revenue) as RUNNING_SUM from sales_subject_area
Result:
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
This function takes a set of records as input and counts the number of records encountered so far.
RCOUNT(expr)
Where:
expr
is an expression of any data type.
In Answers, you can also use the following alternate syntax:
RCOUNT(expression1 [BY expression2[, expression3[, ...]]])
Where:
expression1, expression2, expression3 ...
can be any column reference, or an arithmetic expression on column references.
The BY
clause causes the RCOUNT
computation to restart at the row where the value of any of the BY
columns differs from the previous row.
This example shows a query that uses the RCOUNT
function, along with example query results.
select month, profit, RCOUNT(profit) from sales_subject_area where profit > 200
Result:
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
This function takes a set of records as input and shows the maximum value based on records encountered so far. The specified data type must be one that can be ordered.
RMAX(expr)
Where:
expr
is an expression of any data type. The data type must be one that has an associated sort order.
In Answers, you can also use the following alternate syntax:
RMAX(expression1 [BY expression2[, expression3[, ...]]])
Where:
expression1, expression2, expression3 ...
can be any column reference, or an arithmetic expression on column references.
The BY
clause causes the RMAX
computation to restart at the row where the value of any of the BY
columns differs from the previous row.
This example shows a query that uses the RMAX
function, along with example query results.
SELECT month, profit, RMAX(profit) from sales_subject_area
Result:
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
This function takes a set of records as input and shows the minimum value based on records encountered so far. The specified data type must be one that can be ordered.
RMIN(expr)
Where:
expr
is an expression of any data type. The data type must be one that has an associated sort order.
In Answers, you can also use the following alternate syntax:
RMIN(expression1 [BY expression2[, expression3[, ...]]])
Where:
expression1, expression2, expression3 ...
can be any column reference, or an arithmetic expression on column references.
The BY
clause causes the RMIN
computation to restart at the row where the value of any of the BY
columns differs from the previous row.
This example shows a query that uses the RMIN
function, along with example query results.
select month, profit, RMIN(profit) from sales_subject_area
Result:
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 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:
This function is a time series aggregation function that calculates the aggregated value from the current time back to a specified time period. For example, AGO
can produce sales for every month of the current quarter and the corresponding quarter-ago sales.
Time series functions operate on members of time dimensions which are at or below the level of the function. Because of this, one or more columns that uniquely identify members at or below the given level must be projected in the query. Alternatively, you can apply a filter to the query that specifies a single member at or below the given level. See "About the AGO Function Level" for more information about the level of the function.
Multiple AGO
functions can be nested if all the AGO
functions have the same level argument. You can nest exactly one TODATE
and multiple AGO
functions if they each have the same level argument.
AGO(expr, [time_level], offset)
Where:
expr
is an expression that references at least one measure column.
time_level
is an optional argument that specifies the type of time period, such as quarter, month, or year.
In Answers, specify a presentation level from a presentation hierarchy for time_level
.
offset
is an integer literal that represents the time shift amount.
The following example returns last year's sales:
SELECT Year_ID, AGO(sales, year, 1)
It is recommended that you explicitly specify the level of the AGO
function using the [
time_level
]
argument.
If you do not explicitly specify the [
time_level
]
argument, the default level is determined as follows:
If the measure used in the expression is a level-based measure in the time dimension (as set in the Administration Tool), then that same level is considered the default AGO
level.
Otherwise, the grain of the measure used in the expression, as determined by the BY
clause of the measure shown in the logical request, is the default Ago level.
For example, the result of the query:
SELECT year, AGO(sales, 1) WHERE quarter=1
is the same as:
SELECT year, AGO(sales, year_level, 1) WHERE quarter=1
You can see the default AGO
level for a given query in the Logical Request section of the query log.
This function computes the aggregate of a measure over the period starting x units of time and ending y units of time from the current time. For example, you can use PERIODROLLING
to compute sales for a period that starts at a certain quarter before and ends at a certain quarter after the current quarter.
Time series functions operate on members of time dimensions which are at or below the level of the function. Because of this, one or more columns that uniquely identify members at or below the given level must be projected in the query. Alternatively, you can apply a filter to the query that specifies a single member at or below the given level. See "Determining the Level Used by the PERIODROLLING Function" for more information about the level of the function.
You cannot nest AGO
and TODATE
functions within a PERIODROLLING
function. Also, you cannot nest PERIODROLLING
, FIRST
, FIRST_PERIOD
, LAST
, and LAST_PERIOD
functions.
If you embed other aggregate functions (like RANK
, TOPN
, PERCENTILE
, FILTER
, or RSUM
) inside PERIODROLLING
, the PERIODROLLING
function is pushed inward. For example, PERIODROLLING(TOPN(
measure
))
is executed as TOPN(PERIODROLLING(
measure
))
.
PERIODROLLING(measure, x ,y [,hierarchy])
Where:
measure
is the name of a measure column.
x
is an integer that specifies the offset from the current time. Precede the integer with a minus sign (-) to indicate an offset into the past.
y
specifies the number of time units over which the function will compute. To specify the current time, enter 0.
hierarchy
is an optional argument that specifies the name of a hierarchy in a time dimension, such as yr, mon, day
, that you want to use to compute the time window. This option is useful when there are multiple hierarchies in a time dimension, or when you want to distinguish between multiple time dimensions.
If you want to roll back or forward the maximum possible amount, use the keyword UNBOUND
. For example, the function PERIODROLLING (
measure
, -UNBOUND, 0)
sums over the period starting from the beginning of time until now.
You can combine PERIODROLLING
and AGGREGATE AT
functions to specify the level of the PERIODROLLING
function explicitly. For example, if the query level is day but you want to find the sum of the previous and current months, use the following:
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)
The unit of time (offset) used in the PERIODROLLING
function is called the level of the function. This value is determined by the measure level of the measures in its first argument and the query level of the query to which the function belongs. The measure level for the measure can be set in the Administration Tool. If a measure level has been set for the measure used in the function, the measure level is used as the level of the function. The measure level is also called the storage grain of the function.
If a measure level has not been set in the Administration Tool, then the query level is used. The query level is also called the query grain of the function. In the following example, the query level is month, and the PERIODROLLING
function computes the sum of the last, current, and next month for each city for the months of March and April:
SELECT year, month, country, city, PERIODROLLING(sales, -1, 1) WHERE month in ('Mar', 'Apr') AND city = 'New York'
When there are multiple hierarchies in the time dimension, you must specify the hierarchy
argument in the PERIODROLLING
function. For example:
SELECT year, fiscal_year, month, PERIODROLLING(sales, -1, 1, "fiscal_time_hierarchy")
In this example, the level of the PERIODROLLING
function is fiscal_year
.
This function is a time series aggregation function that aggregates a measure from the beginning of a specified time period to the currently displayed time. For example, this function can calculate Year to Date sales.
Time series functions operate on members of time dimensions which are at or below the level specified in the function. Because of this, one or more columns that uniquely identify members at or below the given level must be projected in the query. Alternatively, you can apply a filter to the query that specifies a single member at or below the given level.
A TODATE
function may not be nested within another TODATE
function. You can nest exactly one TODATE
and multiple AGO
functions if they each have the same level argument.
TODATE
is different from the TO_DATE
SQL function supported by some databases. Do not use TO_DATE
to change to a DATE
data type. Instead, use the CAST
function. See "CAST" for more information.
TODATE(expr, time_level)
Where:
expr
is an expression that references at least one measure column.
time_level
is the type of time period, such as quarter, month, or year.
The following example returns the year-to-month sales:
SELECT Year_ID, Month_ID, TODATE(sales, year)
String functions perform various character manipulations, and they operate on character strings. Functions include:
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.
ASCII(strExpr)
Where:
strExpr
is any expression that evaluates to a character string.
This function returns the length, in bits, of a specified string. Each Unicode character is 2 bytes in length (equal to 16 bits).
BIT_LENGTH(strExpr)
Where:
strExpr
is any expression that evaluates to character string.
This function converts a numeric value between 0 and 255 to the character value corresponding to the ASCII code.
CHAR(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value between 0 and 255.
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.
CHAR_LENGTH(strExpr)
Where:
strExpr
is any expression that evaluates to a character string.
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.
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.
This example request returns the results shown.
SELECT DISTINCT CONCAT('abc','def' || 'ghi') FROM employee
Result:
abcdefghi
This function inserts a specified character string into a specified location in another character string.
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.
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 ...
Returns a specified number of characters from the left of a string.
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.
This example returns the three leftmost characters from the character string 123456:
SELECT LEFT('123456', 3) FROM table
Result:
123 123 ...
This function returns the length, in number of characters, of a specified string. The length is returned excluding any trailing blank characters.
LENGTH(strExpr)
Where:
strExpr
is any expression that evaluates to a character string.
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, include the integer argument. 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.
LOCATE(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. The integer argument is optional.
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')
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.
LOCATE('d' 'abcdef', 3)
This example returns 0, because the letter b occurs in the string before the starting position to begin the search.
LOCATE('b' 'abcdef', 3)
This function converts a character string to lowercase.
LOWER(strExpr)
Where:
strExpr
is any expression that evaluates to a character string.
This function returns the number of bits, in base 8 units (number of bytes), of a specified string.
OCTET_LENGTH(strExpr)
Where:
strExpr
is any expression that evaluates to a character string.
This function returns the numeric position of strExpr1
in a character expression. If strExpr1
is not found, the function returns 0. See also "LOCATE" for related information.
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.
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')
This function repeats a specified expression n times.
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.
This example repeats abc four times:
REPEAT('abc', 4)
This function replaces one or more characters from a specified character expression with one or more other characters.
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.
In the character string abcd1234, the characters 123 are replaced by the character string zz:
Replace('abcd1234', '123', 'zz')
Result:
abcdzz4
This function returns a specified number of characters from the right of a string.
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.
This example returns the three rightmost characters from the character string 123456:
SELECT right('123456', 3) FROM table
Result:
456
This function inserts blank spaces.
SPACE(integer)
Where:
integer
is any positive integer that indicates the number of spaces to insert.
This function creates a new string starting from a fixed number of characters into the original string.
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.
This function strips specified leading and trailing characters from a character string.
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.
This function strips specified leading characters from a character string.
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.
This function strips specified trailing characters from a character string.
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.
The math functions perform mathematical operations. Functions include:
This function calculates the absolute value of a numeric expression.
ABS(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the arc cosine of a numeric expression.
ACOS(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the arc sine of a numeric expression.
ASIN(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the arc tangent of a numeric expression.
ATAN(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the arc tangent of y/x, where y is the first numeric expression and x is the second numeric expression.
ATAN2(numExpr1, numExpr2)
Where:
numExpr
is any expression that evaluates to a numeric value.
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.
CEILING(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the cosine of a numeric expression.
COS(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the cotangent of a numeric expression.
COT(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function converts an expression from radians to degrees.
DEGREES(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function sends the value to the power specified.
EXP(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
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).
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.
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.
FLOOR(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the natural logarithm of an expression.
LOG(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the base 10 logarithm of an expression.
LOG10(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function divides the first numeric expression by the second numeric expression and returns the remainder portion of the quotient.
MOD(numExpr1, numExpr2)
Where:
numExpr
is any expression that evaluates to a numeric value.
This example request returns a value of 0:
MOD(9, 3)
This example request returns a value of 1:
MOD(10, 3)
This function returns the constant value of pi (the circumference of a circle divided by its diameter).
PI()
This function takes the first numeric expression and raises it to the power specified in the second numeric expression.
POWER(numExpr1, numExpr2)
Where:
numExpr1
is any expression that evaluates to a numeric value.
This function converts an expression from degrees to radians.
RADIANS(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
Returns a pseudo-random number based on a seed value. For a given seed value, the same set of random numbers are generated.
RAND(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function rounds a numeric expression to n digits of precision.
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.
This example returns 2.17 as the result.
ROUND(2.166000, 2)
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.
SIGN(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the sine of a numeric expression.
SIN(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function calculates the square root of the numeric expression argument. The numeric expression must evaluate to a nonnegative number.
SQRT(numExpr)
Where:
numExpr
is any expression that evaluates to a nonnegative numeric value.
This function calculates the tangent of a numeric expression.
TAN(numExpr)
Where:
numExpr
is any expression that evaluates to a numeric value.
This function truncates a decimal number to return a specified number of places from the decimal point.
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.
This example returns 45.12:
TRUNCATE(45.12345, 2)
This example returns 25.12:
TRUNCATE(25.126, 2)
The calendar date/time functions manipulate data of the data types DATE
and DATETIME
based on a calendar year. You must select these functions with another column; they cannot be selected alone.
Month and day names are returned in mixed case by default. If you have existing expressions that assume month/day names are returned in uppercase, you must either adjust the expressions, or set the parameters USE_UPPERCASE_MONTH_NAMES
and USE_UPPERCASE_DAY_NAMES
to YES
in NQSConfig.INI. See "NQSConfig.INI File Configuration Settings" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.
Functions such as MONTHNAME can be used to format date columns, but the locale is fixed by the data source or the Oracle BI Server's NQSConfig.INI file. It is not possible for you to specify a locale for the logical SQL date formatting functions because these are based on the ODBC standard, which does not provide for a locale argument.
To format date columns according to the user's selected locale, Oracle suggests that when the locale is fixed, the content designer specifies a standard date format or a custom date format on an analysis' date column. See "Custom Format Strings for Date and Time Fields" and "Column Properties: Data Format tab" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition
Functions include:
This function returns the current date. The date is determined by the system in which the Oracle BI Server is running.
CURRENT_DATE
This function returns the current time. The time is determined by the system in which the Oracle BI Server is running.
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.
This function returns the current date/timestamp. The timestamp is determined by the system in which the Oracle BI Server is running.
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.
This function returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date.
DAY_OF_QUARTER(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
This function returns the name of the day of the week for a specified date.
DAYNAME(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
This function returns the number corresponding to the day of the month for a specified date.
DAYOFMONTH(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
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.
DAYOFWEEK(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
This function returns the number (between 1 and 366) corresponding to the day of the year for a specified date.
DAYOFYEAR(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
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.
HOUR(timeExpr)
Where:
timeExpr
is any expression that evaluates to a time.
This function returns a number (between 0 and 59) corresponding to the minute for a specified time.
MINUTE(timeExpr)
Where:
timeExpr
is any expression that evaluates to a time.
This function returns the number (between 1 and 12) corresponding to the month for a specified date.
MONTH(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
This function returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date.
MONTH_OF_QUARTER(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
This function returns the name of the month for a specified date.
MONTHNAME(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
This function returns the current timestamp. The NOW
function is equivalent to the CURRENT_TIMESTAMP
function.
NOW()
This function returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date.
QUARTER_OF_YEAR(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
This function returns the number (between 0 and 59) corresponding to the seconds for a specified time.
SECOND(timeExpr)
Where:
timeExpr
is any expression that evaluates to a time.
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.
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.
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;
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.
Note:
This section describes the TIMESTAMPDIFF
behavior when the function is calculated in the Oracle BI Server. If this function is calculated in the data source, then the result might be different from the behavior described in this section. If the TIMESTAMPDIFF
function result is different from the desired result, then you can disable TIMESTAMP_DIFF_SUPPORTED
in the Features tab for the database object in the Administration Tool to ensure that the function is calculated in the Oracle BI Server. However, making this change might adversely affect performance.
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.
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.
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;
This function returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date.
WEEK_OF_QUARTER(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
This function returns a number (between 1 and 53) corresponding to the week of the year for the specified date.
WEEK_OF_YEAR(dateExpr)
Where:
dateExpr
is any expression that evaluates to a date.
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:
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.
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)
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.
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
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 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")
Users and administrators can create requests by directly calling database functions from either Oracle BI Answers, or by using a logical column (in the logical table source) within the metadata repository. Key uses for these functions include the ability to pass through expressions to get advanced calculations, as well as the ability to access custom written functions or procedures on the underlying database.
Functions include:
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.
The ability to use EVALUATE
is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.
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.
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]) }'
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.
The ability to use EVALUATE_ANALYTIC
is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.
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.
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)
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.
The ability to use EVALUATE_AGGR
is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.
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.
EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)
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.
The ability to use EVALUATE_PREDICATE
is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.
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
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;