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.
Reference: Supported SQL Statements
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
- INSERT, UPDATE,
and DELETE for relational, IMS, and FOCUS data sources.
- Equijoins and
- Outer joins,
subject to certain restrictions. See SQL Joins.
- CREATE VIEW
and DROP VIEW.
- PREPARE and
- 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.
subqueries for all requests in the WHERE predicate and in the FROM
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,
- =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,
- NULL and NOT
- LIKE and NOT
- IN and NOT
- Date and time
- 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.
Decimal Notation (CDN) when the CDN variable is set.
- National Language
Reference: Unsupported SQL Statements
Translation Services does not support the following:
- More than 15
joins per SELECT. This limit is set by SQL. FOCUS supports up to
- 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
subqueries for DML Generation.
Reference: SQL Translator Reserved Words
following words may not be used as field names in a Master File
that is used with the SQL Translator: