SQL Tips and Techniques for Client Access ODBC

Resolving The Problem

ORDER BY Column Not in the Result Set

Some ODBC applications attempt to use a field in the ORDER BY clause that does not exist in the result set. This results in the following error:

SQL0208 - ORDER BY column 'column_name' not in result

Support for this feature was added in IBM® DB2® UDB for iSeries™ in V5R2. Client Access ODBC reports support for this to applications through the SQLGetInfo API: SQL_ORDER_BY_COLUMNS_IN_SELECT=90. Note that only V5R2 and later (with a service pack after SI04684) report "N". Older versions report "Y" informing applications that the column used in the ORDER BY clause must be in the select list.

SQL0122 - Column or Function not valid

SQL0122 - Column &1 or function specified in SELECT list not valid. When using a GROUP BY clause, all of the columns in the SELECT list must be in the GROUP BY clause or be contained within a column function (note that this is different than being used in a scalar function) as explained in the SQL Reference as follows:

If GROUP BY or HAVING is used:

o	Each column name in the select list must either identify a grouping column or be specified within a column function.
o	The RRN, PARTITION, NODENAME, and NODENUMBER functions cannot be specified in the select list.
o	The select list is applied to each group of R, and the result contains as many rows as there are groups in R. When the select list is applied to a group of R, that group is the source of the arguments of the column functions in the select list.

Example:

"Select workdept, salary from employee GROUP BY workdept" would fail because salary is not in the GROUP BY.
"Select field1,DATE(field2) from T1 GROUP BY field1" fails because field2 is not in a column function or the GROUP BY.
"Select workdept, avg(salary) from employee GROUP BY workdept order by 2" would work fine because workdept is a grouping column and salary is specified in a column function (AVG).

CAST Function

One common use of the CAST function is with UNIONS. It can be used to cast between two different data types or supply a default value for a field missing in one table. Some query building tools will not use the CAST because older versions of the ODBC driver (V4R5 and earlier) can incorrectly report that CAST is not supported. In this case, upgrade Client Access or modify the query manually.

Example:

Select f1, f2 from T1 UNION select f1, cast(null as float) from T2

Object Name Not Valid For Naming Option

SQL5016 - Object name &1 not valid for naming option.

This should be very straight forward. Verify that the naming convention specified on the ODBC datasource (SQL versus System) is correct for the syntax used in the SQL statement. With SQL naming, the qualified form of an object name is authorization-name.object-name; for system naming, it is library/object-name.

Decimal Separator

Some users have set the decimal separator parameter of the ODBC connection to a comma rather than a period. This gives an unusual SQL0104 error where the invalid token is usually the decimal point in the data:

"[IBM][Client Access ODBC Driver (32-bit)][DB2/400 SQL] SQL0104 - Token . was not valid. Valid tokens: ) ,."

The problem can be harder to detect if the application did not use a space between each field. For example, consider the SQL statement INSERT INTO T1 VALUES ( 20,10,10.2) where field 1 is Integer, field 2 and 3 are both numerics with a non-zero scale. In this case, the invalid token would appear as ",10".

Correct the setting in the data source or the application (if the connect string overrides the data source).

References

For additional information, refer to the following Rochester Support Center publications:

DB2 UDB for iSeries Database Programming, which is available at the following Web site:

http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/dbp/rbafopdf.pdf?view=kc

DB Universal Database for iSeries SQL Reference, which is available at the following Web site:

http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafzpdf.pdf?view=kc