Syntax and Usage Notes for the SELECT Statement
The SELECTSELECT 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.
Basic Syntax for the SELECT Statement
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.
FROM from_clause is the list of tables in the request. Optionally includes
certain join information for the request.
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.
GROUP BY column {, column} specifies a column (or alias) belonging to a table
defined in the data source.
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.
Usage Notes
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
Subquery Support
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 EXISTSIn 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.
SELECT List Syntax
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.
Syntax
...
* |
(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.
FROM Clause Syntax
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.
WHERE Clause Syntax
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 EXISTSGROUP BY Clause Syntax
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 SELECTGROUP BY clause with different columns and the Oracle BI Server computes
the results based on the level specified in the GROUP BY clause.
ORDER BY Clause Syntax
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