Sql Db2 Limit Rows

gandalf33 · #1 (**permalink**) 04-15-02, 13:10

For reasons that are simply too long to get into here, I simply would like to know if there is a db2 compliant SQL keyword or function to have a query stop when it has retrieved a certain amount of rows. In my case, seven. I know I can use WHERE clauses to limit my resultsets based on evaluating values of fields and such. But that is not what I am looking for. I just need to know if there is an SQL ROWCOUNT type variable or keyword that works with db2 to just have a query stop when it has attained seven rows. I would appreciate the help. My query is being run from a Servlet.

WPSullivan · #2 (**permalink**) 04-15-02, 16:15

Why yes there is:

Add:

Code:

FETCH FIRST n ROWS ONLY

to the end of your SQL Select string and the ResultSet will end (return false on next()) once n rows have been navigated. You can see this in use using the SAMPLE database:

Code:

Ohare:wpsulliv: db2 connect to SAMPLE user db2inst1
Enter current password for db2inst1:

   Database Connection Information

 Database server        = DB2/6000 7.2.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

Ohare:wpsulliv: db2 "select * from EMPLOYEE fetch first 5 rows only"

EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT PHONENO HIREDATE   JOB
 EDLEVEL SEX BIRTHDATE  SALARY      BONUS       COMM
------ ------------ ------- --------------- -------- ------- ---------- --------
 ------- --- ---------- ----------- ----------- -----------
000010 CHRISTINE    I       HAAS            A00      3978    01/01/1965 PRES
      18 F   08/24/1933    52750.00     1000.00     4220.00
000020 MICHAEL      L       THOMPSON        B01      3476    10/10/1973 MANAGER
      18 M   02/02/1948    41250.00      800.00     3300.00
000030 SALLY        A       KWAN            C01      4738    04/05/1975 MANAGER
      20 F   05/11/1941    38250.00      800.00     3060.00
000050 JOHN         B       GEYER           E01      6789    08/17/1949 MANAGER
      16 M   09/15/1925    40175.00      800.00     3214.00
000060 IRVING       F       STERN           D11      6423    09/14/1973 MANAGER
      16 M   07/07/1945    32250.00      500.00     2580.00

  5 record(s) selected.

Hope this helps,

WP.

gandalf33 · #3 (**permalink**) 04-15-02, 17:11

Thank You. I have not had the chance to check on it but I am sure that it will work.

karar · #4 (**permalink**) 12-18-02, 07:29

I am also facing the same problem as one of the user posted for it earlier. I am using DB2 on AS400 machine. I want to fetch specific number of records from the database. When I use the following sql query on other databases, it works well:-
select * from LIBRARY/TABLE/ limit 0,10
I also checked for the following query as well, but it also failed to work:-
select * from LIBRARY/TABLE/ fetch first 10 rows only
Kindly help me in telling some other alternative query.
Thank You

karar · #5 (**permalink**) 06-13-06, 16:56

I found an SQL syntax which seems solving the issue posted in this thread. This is a very old thread, but may be this could be helpful for other users who are stuck in same scenario.

select * from
(SELECT Emp_No, First_Name, Last_Name, Deptt, ROW_NUMBER() OVER () AS RN
FROM EMP) AS col
where RN between 1 and 7;

I hope the above SQL Query would solve in the current situation plus in some complex situation where 'PREVIOUS PAGE', 'NEXT PAGE' features are to be developed. It can also provide the direct fetching of intermediate pages as can be found in the 'google search' (please see attached JPG document).

Thank you.

fuDDel · #6 (**permalink**) 11-23-09, 08:07

Hi,

I'm using the sample that karar posted but I got a problem with it.
If I'm using the UPPER in the SQL I'll get an error.
Sample:

Code:

select * from
(SELECT Emp_No, First_Name, Last_Name, Deptt, ROW_NUMBER() OVER () AS RN
FROM EMP 
WHERE upper(First_Name) like upper('name&#37;')
) AS col
where RN between 1 and 7;

Does someone have a workaround for that problem?

greetings.

P.S.: Sorry for my bad english but I'm a kraut :-D

n_i · #7 (**permalink**) 11-23-09, 08:19

Depending on the DB2 version, you may not be allowed to use functions on the right hand side of the LIKE operator - only string literals. Generally it is advisable to indicate your DB2 version, platform, and the exact error code and message when asking such questions.

fuDDel · #8 (**permalink**) 11-23-09, 08:42

Hey,

thanks for the reply.
I'm using DB2 on an AS/400(i5)-Server.
The version number is the same as the operating system i guess. So it's V5R4M0.

error Code: SQL0255
error message(translated by google):
Function not supported for the Query. Reason Code: 6
6: Code 6 - An OLAP function is not supported for this query.

n_i · #9 (**permalink**) 11-23-09, 10:45

According to the manual (http://publib.boulder.ibm.com/infoce...ubselect.htm):

Quote:

If [...] table-reference identifies a distributed table, a table that has a read trigger, a DDS-created logical file, or logical file built over multiple physical file members; the query cannot contain:

* EXCEPT or INTERSECT operations,
* OLAP specifications,
* recursive common table expressions,
* ORDER OF, or
* scalar fullselects (scalar subselects are supported).

Internet Services

Web Development

Digital Marketing

Consumer Tech