Supported and Unsupported SQL Statements
SQL Translation Services is compliant with ANSI Level
2. This facility supports many, but not all, SQL statements. The Reporting Server and specific RDBMS
engines may also support the alpha1 CONCAT alpha2 syntax.
See Supported SQL Statements and Unsupported SQL Statements.
Many of the supported SQL statements are
candidates for Dialect Translation. This feature enables a server
to route inbound SQL requests to SQL-capable subservers and data adapters
where possible. Dialect Translation avoids translation to the Reporting
Server Data Manipulation Language (DML), while maintaining data
location transparency. It transforms a standard SQL statement into
one that can be processed by the destination SQL engine, while preserving
the semantic meaning of the statement.
Note: Because the SQL Translator is ANSI Level 2 compliant,
some requests that worked in prior releases may no longer work.
x
Reference: Supported SQL Statements
SQL
Translation Services supports the following:
- SELECT, including
SELECT ALL and SELECT DISTINCT.
- CREATE TABLE.
The following data types are supported for CREATE TABLE: REAL, DOUBLE
PRECISION, FLOAT, INTEGER, DECIMAL, CHARACTER, SMALLINT, DATE, TIME, and
TIMESTAMP.
- INSERT, UPDATE,
and DELETE for relational, IMS, and FOCUS data sources.
- Equijoins and
non-equijoins.
- Outer joins,
subject to certain restrictions. See SQL Joins.
- CREATE VIEW
and DROP VIEW.
- PREPARE and
EXECUTE.
- Delimited identifiers
of table names and column names. Table and column names containing
embedded blanks or other special characters in the SELECT list should
be enclosed in double quotation marks.
- Column names
qualified by table names or by table tags.
- The UNION [ALL],
INTERSECT [ALL], and EXCEPT [ALL] operators.
- Non-correlated
subqueries for all requests in the WHERE predicate and in the FROM list.
- Correlated
subqueries for requests that are candidates for Dialect Translation
to an RDBMS that supports this feature. Note that correlated subqueries
are not supported for FOCUS and other non-relational data sources.
- Numeric constants,
literals, and expressions in the SELECT list.
- Scalar functions
for queries that are candidates for Dialect Translation if the RDBMS
engine supports the scalar function type. These include: ABS, CHAR, CHAR_LENGTH,
CONCAT, COUNTBY, DATE, DAY, DAYS, DECIMAL, EDIT, EXTRACT, FLOAT,
HOUR, IF, INT, INTEGER, LCASE, LENGTH, LOG, LTRIM, MICROSECOND, MILLISECOND,
MINUTE, MONTH, POSITION, RTRIM, SECOND, SQRT, SUBSTR (or SUBSTRING),
TIME, TIMESTAMP, TRIM, VALUE, UCASE, and YEAR.
- The concatenation
operator, '||', used with literals or alphanumeric columns.
- The following
aggregate functions: COUNT, MIN, MAX, SUM, and AVG.
- The following
expressions can appear in conditions: CASE, NULLIF, and COALESCE.
- Date, time,
and timestamp literals of several different formats. See SQL Translator Support for Date, Time, and Timestamp Fields.
- All requests
that contain ANY, SOME, and ALL that do not contain =ALL, <>ANY, and
<>SOME.
- =ALL, <>ANY,
and <>SOME for requests that are candidates for Dialect Translation if
the RDBMS engine supports quantified subqueries.
- The special
registers USER, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_EDASQLVERSION,
and CURRENT_TIMEZONE.
- NULL and NOT
NULL predicates.
- LIKE and NOT
LIKE predicates.
- IN and NOT
IN predicates.
- Date and time
arithmetic.
- EXISTS and
NOT EXISTS predicates.
- GROUP BY clauses
expressed using explicit column names.
- ORDER BY clauses
expressed using explicit column names or column numbers.
- FOR FETCH ONLY
feature to circumvent record locking.
- Continental
Decimal Notation (CDN) when the CDN variable is set.
- National Language
Support (NLS).
x
Reference: Unsupported SQL Statements
SQL
Translation Services does not support the following:
- More than 15
joins per SELECT. This limit is set by SQL. FOCUS supports up to
16 joins.
- ALIAS names
in Master Files and the use of formatting options to format output.
- Unique truncations
of column names.
- Temporary defined
columns. Permanent defined columns, defined in the Reporting
Server Dynamic Catalog or in the Master File, are supported.
- Correlated
subqueries for DML Generation.
x
Reference: SQL Translator Reserved Words
The
following words may not be used as field names in a Master File
that is used with the SQL Translator:
- ALL
- COUNT
- SUM
- MAX
- MIN
- AVG
- CURRENT
- DISTINCT
- USER