Row limiting clause

Informix®

Informix SQL supports the SKIP and FIRST/LIMIT keywords to limit the number of rows of a result set:

SELECT SKIP 10 FIRST 20 customer.* FROM customer ... ORDER BY cust_name

This Informix SQL syntax is not portable.

Recent database engines support the row limiting clause syntax defined by the SQL standard:

SELECT ... OFFSET n ROWS FETCH FIRST m ROWS ONLY

This should be the prefered syntax to be used, if all target database types support this SELECT clause.

The ODI database drivers can convert the Informix SQL SKIP/FIRST row limiting clause to a native SQL equivalent, if the row limiting clause parameters are simple integer literals (the clause is not translated when using SQL parameters / program variables).

Important: In addition to the SKIP/FIRST clause of the projection clause, Informix SQL supports also a LIMIT clause after the ORDER BY clause:

SELECT customer.* FROM customer ... ORDER BY cust_name LIMIT 10

This Informix SQL syntax construction is not converted by the ODI drivers. To benefit from the conversion, review the code to use the Informix SQL SKIP/FIRST clause instead.

SAP ASE

SAP® ASE supports the following row limiting clause:

SELECT TOP(m) ... FROM ...

Solution

Important: The Informix SQL row limiting clause is not converted by the SAP ASE driver, because the natice SQL syntax does not support an offset option.