How to select a specific number of rows in CA-IDMS/SQL

Document ID : KB000020054
Last Modified Date : 11/10/2018
Show Technical Document Details
Introduction:

The document describes a method for issuing an SQL select which will return a user-specified number of rows.

Background:
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.
Environment:
CA-IDMS, all supported releases.
Instructions:

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.

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
Additional Information:
SQL Reference