Java / DB2 FETCH FIRST: n ROWS with custom parameter

Question

So this is what we are looking at, I want to be able to pass a parameter to my fetch clause so that I have a request with a custom FETCH sum.

From what I read in other SOs, this is unfortunately not available through the DB2 database. However, the answers I saw were not very good, so I ask a question for my specific situation.

Other options I tried work DO:

• Concatenating String statements to create my custom query.
• Instead of using FETCH use WHERE col_1 BETWEEN value1 AND value2
• As @bhamby commented, you can also replace BETWEEN stmt using> and <as part of the WHERE clause

To give you a basic example of what I'm looking for (I use Spring JDBC with named btw parameters)

private static final String SQL_FETCH_NEXT = ""
    + "SELECT * " 
    + "FROM ( SELECT "
    + "     (ROW_NUMBER() OVER(ORDER BY ID, AGE)) AS RUNNING_NO, ID, NAME, AGE "
    + "         FROM DATABASE1.TABLE1 PERSON "
    + "     ) AS TABLE_ONE "
    + "WHERE TABLE_ONE.RUNNING_NO > :rowNumber "
    + "ORDER BY TABLE_ONE.RUNNING_NO "
    + "FETCH FIRST :fetchCount ROWS ONLY ";

So, in my query above (which doesn't work because of: fetchCount), how can I get my own parameter (or a descent replacement) for the FETCH sum?

Answer 1

You already have everything you need as you are already using ROW_NUMBER(). Why don't you try this:

SELECT *
FROM (
    SELECT
         ROW_NUMBER() OVER(ORDER BY ID, AGE) AS RUNNING_NO
        ,ID
        ,NAME
        ,AGE
    FROM DATABASE1.TABLE1 PERSON
) AS TABLE_ONE
WHERE TABLE_ONE.RUNNING_NO  > :rowNumber
  AND TABLE_ONE.RUNNING_NO <= :rowNumber + :fetchCount
ORDER BY TABLE_ONE.RUNNING_NO

Answer 2

Row_Number() , FETCH FIRST N ROWS , row_number(), , , row_number.

, , , , ( ). , . ( row_number ).

, :

Java, SQL- , "Fetch first N rows only".

, , DAO ( - 1 * ).

, SQL- :

private static final String SQL_FETCH_NEXT = ""
    + " SELECT ID, NAME, AGE "
    + " FROM DATABASE1.TABLE1 PERSON "
    + " ORDER BY ID, AGE ";

private static Object runQuery(int pageNum, String filterText) {

//boilerplate code skipped

String sql = SQL_FETCH_NEXT + " fetch first " + pageNum * VALUES_PER_PAGE + " rows only ";

//the rest of the method

}

FETCH FIRST N ROWS : . , , , ROW_NUMBER . AGE ( , ). , , , .

, , , , , .

, , ( pageNumber * itemsPerPage). , , , , .

, , , . , , : , (ROW_NUMBER) , (pageNumber-1) * itemsPerPage .

: ROW_NUMBER. , .

, ( , ). .

, , , .

, :

• , , . , Java.
• , (pageNumber-1) * itemsPerPage , , , ( java).