limiting number of rows returned in a cursor select

[BF]

Hello,
I am trying to find out if there is a way to limit the number of rows
returned when a cursor is opened. I am using DB2 version 7 on z/OS. The
SELECT statement in my DECLARE CURSOR statement could potentially return
400,000+ rows, but I really only want the first 15 records found. I can put
a lower-bound (col >= "xxx") in the WHERE clause but cannot specify an
upper-bound (i.e. AND col <= "yyy"); therefore, depending on the value the
user specifies for the "xxx" value, the result-set could have many records,
but I really only want the first 15.

In SQL Server, I can execute a "SET ROWCOUNT 15", then execute my SELECT,
and regardless of how many rows might meet the WHERE clause criteria, the
SELECT will stop after finding 15 rows.

Is there a way in DB2 on z/OS to accomplish this same task?

Thanks.
-Bruce

[Jean-David Beyer]

BF wrote:

Hello,
I am trying to find out if there is a way to limit the number of rows
returned when a cursor is opened. I am using DB2 version 7 on z/OS. The
SELECT statement in my DECLARE CURSOR statement could potentially return
400,000+ rows, but I really only want the first 15 records found. I can put
a lower-bound (col >= "xxx") in the WHERE clause but cannot specify an
upper-bound (i.e. AND col <= "yyy"); therefore, depending on the value the
user specifies for the "xxx" value, the result-set could have many records,
but I really only want the first 15.

In SQL Server, I can execute a "SET ROWCOUNT 15", then execute my SELECT,
and regardless of how many rows might meet the WHERE clause criteria, the
SELECT will stop after finding 15 rows.

Is there a way in DB2 on z/OS to accomplish this same task?

Thanks.
-Bruce

Put a FETCH FIRST CLAUSE in your query?

E.G.,

Example A10: Query the EMP_ACT table and return those project numbers that
have an employee whose salary is in the top 10 of all employees.

SELECT EMP_ACT.EMPNO,PROJNO
FROM EMP_ACT
WHERE EMP_ACT.EMPNO IN
(SELECT EMPLOYEE.EMPNO
FROM EMPLOYEE
ORDER BY SALARY DESC
FETCH FIRST 10 ROWS ONLY
)
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 21:00:00 up 7 days, 6:01, 3 users, load average: 0.12, 0.07, 0.01