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

Document ID : KB000020054
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

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

Solution:

The SQL Option of CA-IDMS/SQL 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 

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.