BF wrote:[color=blue]
> 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
>
>[/color]
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
