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_nameThis 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 ONLYThis 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 10SKIP/FIRST clause
instead.Microsoft™ SQL Server
Microsoft SQL Server supports the following row
limiting
clause:
SELECT ... ORDER BY ... OFFSET n ROWS [ FETCH FIRST m ROWS ONLY ]Solution
The Informix SQL row limiting clause can be converted by the Microsoft SQL Server driver to the native SQL equivalent clause, when the parameters are simple integer literals.
Note: The row limiting clause must not use SQL parameters. Only row
limiting clauses using integer constants will be converted.
The translation of the Informix SQL
row limiting clause can be controlled with the following FGLPROFILE
entry:
dbi.database.dsname.ifxemul.rowlimiting = { true | false }