The following are some of the restrictions to xfODBC’s support of SQL. For more, see the xfODBC and SQL Connection trackers closed with a status of Restriction (https://resources.synergex.com/SiteTrackers).
- The ORDER BY column must be referenced by SELECT column position when accompanied by a GROUP BY clause. See ORDER BY.
- GROUP BY is not supported for UNION clauses.
- Columns specified in a GROUP BY clause must be included in the select list and columns specified in the select list must be included in the GROUP BY clause. See GROUP BY for information.
- Only one ORDER BY clause is allowed with a UNION, and this must follow the final SELECT statement. An ORDER BY clause in a UNION applies to the entire result set.
Additionally, xfODBC does not support the following:
- Field-level access controls. (Instead, xfODBC enables you to set access levels for tables and users. See Managing access with users, groups, and table settings.)
- ALTER TABLE
- CREATE INDEX (supported after the initial CREATE TABLE and before the first INSERT, but at no other time)
- DROP INDEX
- Expressions in ORDER BY column
- “FROM” in the UPDATE [FROM] command
- Multi-table updates
- ROW_NUMBER and the OVER clause
