468,244 Members | 1,860 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,244 developers. It's quick & easy.

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
Nov 12 '05 #1
1 12877
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Christoffer Gurell | last post: by
1 post views Thread by Phil Endecott | last post: by
3 posts views Thread by Sonnich | last post: by
8 posts views Thread by trint | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.