Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2002
    Posts
    2

    Unanswered: Sql Db2 Limit Rows

    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.

  2. #2
    Join Date
    Oct 2001
    Posts
    68
    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.

  3. #3
    Join Date
    Apr 2002
    Posts
    2
    Thank You. I have not had the chance to check on it but I am sure that it will work.

  4. #4
    Join Date
    Dec 2002
    Posts
    2

    DB2 on AS400

    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

  5. #5
    Join Date
    Dec 2002
    Posts
    2

    Sql Db2 Limit Rows

    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.
    Attached Thumbnails Attached Thumbnails google.JPG  

  6. #6
    Join Date
    Nov 2009
    Posts
    2
    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%')
    ) 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

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Nov 2009
    Posts
    2
    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.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    According to the manual (http://publib.boulder.ibm.com/infoce...ubselect.htm):
    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).
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •