SAP Knowledge Base Article - Preview

2578565 - How to use 'LIMIT" and 'OFFSET' SQL function in SAP IQ

Symptom

[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 ;
      -- OR
         Select  * from SalesOrders order by CustomerIQ asc LIMIT 100, 10 ;

[Known Problem]

  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 ;


Read more...

Environment

SAP IQ16.x

Product

SAP IQ 16.0

Keywords

"Skip rows", "retrieve subset rows", "select subset", , KBA , BC-SYB-IQ , Sybase IQ , How To

About this page

This 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.