Eddy_G

CA Tuesday Tip: (CA IDMS) Limiting number of SQL rows returned

Discussion created by Eddy_G Employee on Oct 9, 2013
Latest reply on Oct 10, 2013 by Eddy_G
CA IDMS Tuesday Tip by Ian Hill, Principal Support Engineer for October 8, 2013.

This week's tip comes from Ian Hill, he has provided an interesting SQL statement below.

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.
The following method can be used, as long as there is a unique primary key in the data being selected.
Using the DEMOEMPL SQL schema provided at installation:-

SET SESSION CURRENT SCHEMA DEMOEMPL;
*+ Status = 0 SQLSTATE = 00000
SELECT E.EMP_ID, E.EMP_FNAME, E.EMP_LNAME, S.SEQ
FROM EMPLOYEE E,
(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
WHERE E.EMP_ID = S.EMP_ID
AND S.SEQ <= 5;
*+
*+ 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
*+                                                                
*+ 5 rows processed                                               

The inner SELECT creates a sequence number for the EMPLOYEE table based on the sort sequence of column EMP_ID.

The outer select places a WHERE clause on that sequence number to limit the number of rows returned.

Outcomes