The document describes a method for issuing an SQL select which will return a user-specified number of rows.
The SQL Option of CA-IDMS has no LIMIT clause which some SQL implementations use to ensure that a specific number of rows is returned to the application.This technique uses an inner SELECT which returns a set of values representing each EMP_ID and how many employees exist with an EMP_ID less than or equal to that EMP_ID (column SEQ).
The outer select joins that table with the EMPLOYEE table again, and places a where criteria on the SEQ column to ensure only that number of rows are returned.
For this technique to return the desired result, the column being used (in this case, EMP_ID) must be unique for the table (EMPLOYEE).
SET SESSION CURRENT SCHEMA DEMOEMPL; *+ Status = 0 SQLSTATE = 00000 SELECT E.EMP_ID, E.EMP_FNAME, E.EMP_LNAME, S.SEQ FROM EMPLOYEE E INNER JOIN (SELECT A.EMP_ID, COUNT(*) AS SEQ FROM EMPLOYEE A, EMPLOYEE B WHERE B.EMP_ID<=A.EMP_ID GROUP BY A.EMP_ID) S ON S.EMP_ID=E.EMP_ID WHERE S.SEQ<=6; *+ *+ EMP_ID EMP_FNAME EMP_LNAME SEQ *+ ------ --------- --------- --- *+ 1003 James Baldwin 1 *+ 1034 James Gallway 2 *+ 1234 Thomas Mills 3 *+ 1765 DAVID Alexander 4 *+ 2004 Eleanor Johnson 5 *+ 2010 Cora Parker 6 *+ *+ 6 rows processed
For IDMS 19.0 PTF SO06940 introduced a new SQL Row Limit, see Article 133073: https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=133073