Supported and Unsupported SQL Statements

Reference:

SQL Translation Services is compliant with ANSI Level 2. This facility supports many, but not all, SQL statements. RDBMS engines may also support the alpha1 CONCAT alpha2 syntax. See Supported SQL Statements and Unsupported SQL Statements.

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

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.

    Note that the following functions are not supported by FOCUS for Mainframe: DIGITS, HEX, VARGRAPHIC.

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

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 Master File, are supported.
  • Correlated subqueries for DML Generation.

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
 Information Builders