[Description & Syntax]
SAP IQ supports useful SQL function LIMIT and OFFSET to return subset of rows that satisfy the WHERE clause.
The LIMIT argument must be an integer or integer variable The OFFSET argument must evaluate to a value greater than or equal to 0.
If <offset-expression> is not specified, the default is 0.
The row limitation clause LIMIT <offset-expression>, <limit-expression> is equivalent to LIMIT <limit-expression> OFFSET <offset-expression>.
The LIMIT is disabled by default. Use the RESERVED_KEYWORDS option to enable the LIMIT keyword.
Set Option PUBLIC.RESERVED_KEYWORDS='LIMIT' ;
For Example, following SQL skips 100 rows from result rows and returns next 10 from 101 rows.
Select * from SalesOrders order by CustomerIQ asc LIMIT 10 OFFSET 100 ;
Select * from SalesOrders order by CustomerIQ asc LIMIT 100, 10 ;
When SQL function 'LIMIT' & 'OFFSET' are used together in a SQL, 'LIMIT' & 'OFFSET' function doesn't work correctly.
This incorrect result status depends on number of rows in table. It can be reproduced when base table has more than a few fundred rows.
For Example, following SQLs are same pattern. It's expected to return only 10 rows after skip 10000 rows from result rows.
But when base table has many rows, below SQLs are returned more than 10 rows incorrectly.
Select * from SalesOrders order by CustomerIQ asc LIMIT 10 OFFSET 10000 ;
Select * from SalesOrders order by CustomerIQ asc LIMIT 10000, 10 ;
"Skip rows", "retrieve subset rows", "select subset", , KBA , BC-SYB-IQ , Sybase IQ , How To
About this pageThis is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).
Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.