Skip Headers
Oracle® Business Intelligence Enterprise Edition Help
11g Release 1 (11.1.1)
  Go To Table Of Contents
Contents

SQL Syntax and Semantics

This section explains SQL syntax and semantics. The following topics are included:

Syntax and Usage Notes for the SELECT Statement

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

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

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

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

This section contains the following topics:

Basic Syntax for the SELECT Statement

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 and Usage Notes for SELECT_PHYSICAL

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

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

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

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

  • The SELECT_PHYSICAL command does not require implicit logical transformation

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


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 Integrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

SELECT_PHYSICAL statements are logged as Physical Request entries.

The topics in this section are the following:

Syntax for the SELECT_PHYSICAL Statement

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.

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

Limiting and Offsetting Rows Returned

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.

Syntax for OFFSET Clause 

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.

Syntax for FETCH Clause 

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.

Example 

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


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.

Operators

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

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.

Table D-1 Operators

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.

AND

AND connective, indicating intersection with one or more conditions to form a compound condition.

OR

OR connective, indicating the union with one or more conditions to form a compound condition.

NOT

NOT connective, indicating a condition is not met.

,

Comma, used to separate elements in a list.


Conditional Expressions

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

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.


Note:

In a CASE statement, AND has precedence over OR.


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.


Note:

In a CASE statement, AND has precedence over OR.


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.

Expressing Literals

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

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

Character Literals

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.


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:

Integer Literals

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.

Examples 

234
+2
567934

Decimal Literals

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.

Examples 

1.223
-22.456
+33.456789

Floating Point Literals

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.

Examples 

333.456E-
1.23e+

Variables

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

Syntax 

SET VARIABLE variable_name = variable_value; SELECT_statement

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

Examples 

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

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