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/SQL has no LIMIT clause which some SQL implementations use to ensure that a specific number of rows is returned to the
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
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.