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

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

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.

Environment

CA-IDMS, all supported releases.

Resolution

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 

Additional Information

SQL Reference