Supported and Unsupported SQL Statements

In this section: Supported SQL Statements and Features Unsupported SQL Statements and Features Expected Results From SQL Queries

This section contains a summary of supported and unsupported SQL statements and the expected results from SQL queries.

Supported SQL Statements and Features

SQL Translation 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, and SMALLINT.
• DROP TABLE for relational data sources.
• INSERT, UPDATE, and DELETE for relational data sources, IMS, Adabas, VSAM, and FOCUS data sources.
• Equijoins and non-equijoins.
• 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.
• AS phrase, used in conjunction with SELECT statements.
• The UNION and UNION ALL operators.
• Non-correlated subqueries for all requests.
• Correlated subqueries for requests that are candidates for Dialect Translation to an RDBMS that supports this feature.
• Numeric constants, literals, and expressions of literals in the SELECT list.
• The following scalar functions for all queries: DECIMAL, FLOAT, INTEGER, and SUBSTR (or SUBSTRING).
• The following scalar functions for queries that are candidates for Dialect Translation if the RDBMS engine supports the scalar function type: CHAR, DATE, DAY, DAYS, DIGITS, HEX, HOUR, LENGTH, MICROSECOND, MINUTE, MONTH, SECOND, TIME, TIMESTAMP, VALUE, VARGRAPHIC, and YEAR.
• The concatenation operator, ||, or the syntax alpha1 CONCAT alpha2 used with literals or alphanumeric columns.
• The following column functions: COUNT, MIN, MAX, SUM, and AVG.
• Date literals of formats YYYY-MM-DD, YYYY/MM/DD, MM-DD-YYYY, and MM/DD/YYYY.
• 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.
• IS NULL and IS NOT NULL predicates.
• =NULL and <>NULL predicates.
• LIKE and NOT LIKE predicates.
• IN and NOT IN predicates.
• EXISTS and NOT EXISTS predicates.
• GROUP BY clause expressed using explicit column names.
• ORDER BY clause expressed using explicit column names or column numbers. Note that ORDER BY with UNION supports the column number syntax only.
• FOR FETCH ONLY feature to circumvent record locking.
• Continental Decimal Notation when the CDN variable is set in the server profile.
• National Language Support.
• Outer joins.
• Date arithmetic.

Unsupported SQL Statements and Features

SQL Translation does not support the following:

• More than 15 joins per SELECT.
• Correlated subqueries in DML Generation.
• =ALL, <>ANY, and <>SOME in DML Generation.

Expected Results From SQL Queries

All SQL queries produce Cartesian product style answer sets. In addition, all dates are converted to YYYYMMDD format on output regardless of their original format.