SQL Syntax and Semantics

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

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

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.

Note: You cannot use * to select all columns from the Advanced tab of the Analysis editor in Answers. Instead, you must specify particular columns.

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 EXISTS

GROUP 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 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 Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

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

Syntax for the SELECT_PHYSICAL Statement

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}]

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.

Aggregate Functions Not Supported in SELECT_PHYSICAL Queries

The following aggregate functions are not supported in SELECT_PHYSICAL queries:

• AGO

• BOTTOMN

• FILTER

• FIRST

• LAST

• RCOUNT

• RMAX

• RMIN

• RSUM

• TODATE

• TOPN

Queries Supported by SELECT_PHYSICAL

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
  

Using the NATURAL_JOIN Keyword

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.

Special Usages of SELECT_PHYSICAL

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";

Limitations of the FETCH and OFFSET Clauses

Because ORDER BY clauses are ignored in UNION ALL set-operator blocks, using these clauses in such queries would be non-deterministic. Do not use FETCH and OFFSET with these queries.

SQL Logical 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

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.

CASE (Switch)

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.

Syntax 

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.

Example 

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.

CASE (If)

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.

Syntax 

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.

Example 

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.

Character Literals

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.

Examples 

'Oracle BI Server'

'abc123'

Datetime Literals

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.

Examples 

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

Numeric Literals

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.

Numeric literals include:

• Integer Literals

• Decimal Literals

• Floating Point Literals

234
+2
567934

1.223
-22.456
+33.456789

333.456E-
1.23e+