hellohatim
Beginner
Joined: 12 Apr 2007
Posts: 28
Topics: 7
|
 Posted: Mon Dec 22, 2008 9:55 am Post subject: INSERT INTO ... SELECT & Pagination logic |
 |
|
Hi All,
I am working on DB2 (ver 8 I guess!) z/OS Stored Procedures and had a requirement to insert data from one table to another. I want to select the last ten rows of the query and insert it in the table. I tried giving a nested SELECT query inside INSERT SQL statement but it does not accept "FETCH FIRST 10 ROWS ONLY". As of now I have to fetch all the 10 rows in Working Storage variables and then insert them seperately into the table.
The insert statement I tried was something like this...
| Code: | INSERT INTO SESSION.TEMP (A, B, C, D)
SELECT A, B, C, D
FROM P72.PARTNER_MERCHANT
WHERE ... |
This does not work if I add "ORDER BY A DESC FETCH FIRST 10 ROWS ONLY" to the nested SELECT Query. The SELECT query with FETCH FIRST 10 works well standalone. Believe there is no such thing as ROW_NUMBER() as well to help me achieve this.
- Is there a way to achieve the desired result in a single SQL query?
- In larger context, this piece of code is for pagination logic for retrieving search results to be displayed on the web page. Currently we are populating a global temporary table with 10 records each (which is the size of the page). A cursor to this table with SELECT * is openened and the same is then passed on as a result set (to Java front end). Every time the user moves to the next page, the query is re-executed and then table is dropped and refilled with the new contents. Is there a better way to achieve such pagination logic (either on the SP or Java side)? I read about scrollable cursors, but as of now I am not aware about its exact working. The search results can run into thousands of rows ... efficiency & speed is also a factor we would need to consider.
Regards,
Hatim
_________________
-Hatim M P |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
