x

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

x

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.
• =ALL, <>ANY, and <>SOME for DML Generation.

x

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