MVSFORUMS.com

hellohatim · Beginner Joined: 12 Apr 2007 Posts: 28 Topics: 7

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...

kolusu

hellohatim · Beginner Joined: 12 Apr 2007 Posts: 28 Topics: 7

Hi Kolusu,

Yes, the select query (as mentioned by you above) works fine on its own, but when I use it in the INSERT SQL statement, it does not.

As I said the requirement is to selectively insert rows from one table to another. Currently I have to do this one by one, involves multiple DB2 call, 10 for FETCH and 10 for INSERT.

Please do guide me if possible on my second question as well!

Thanks!
Hatim
_________________
-Hatim M P

Nic Clouston · Posted: Mon Dec 22, 2008 1:27 pm Post subject:

INSERT is a completely different statement from SELECT - it is not a part of SELECT so of course you have to do it seperately.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

hellohatim · Beginner Joined: 12 Apr 2007 Posts: 28 Topics: 7

Thanks Nic!
_________________
-Hatim M P

Dibakar · Posted: Mon Dec 29, 2008 5:29 pm Post subject:

Hatim what error are you getting? I tried the following code (without the where clause) and it worked -

Nic Clouston · Posted: Tue Dec 30, 2008 4:06 am Post subject:

Diba

Do not worry - I got a little confused about the original code. I do not think it was the festivities, just plain confusion.

Still although you CAN, as you show, use a SELECT clause to get the data for insertion the SELECT statement is an SQL keyword on the same level as INSERT and is not a sub-keyword of INSERT even though it looks like it is.

I hope you understand what I am saying!
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

kolusu · Posted: Tue Dec 30, 2008 2:31 pm Post subject:

Dibakar,

Insert with fetch first n rows only is NOT a valid syntax on Z/os DB2 yet

Kolusu

Dibakar · Posted: Wed Dec 31, 2008 1:17 am Post subject:

Kolusu,

You are right. I don't have access to DB2 on Z/OS so I ran the query on Windows version. I should have clarified it first.

Diba.

kolusu · Posted: Tue Jan 06, 2009 8:27 pm Post subject:

hellohatim,

I just stumbled upon a cheat for this . Try this and let me know if it works. Assume that the WHERE condition on table P72.PARTNER_MERCHANT has fetched 1000 rows we would insert them all in to the session.temp, however since you only want to see the first 10 rows we use the fetch first 10 rows only on final table after insert. This would eliminate the 2 step process of inserting and then performing a select. This logic would help you to perform the page logic also. This works on Db2 V8

hellohatim · Beginner Joined: 12 Apr 2007 Posts: 28 Topics: 7

Hi Kolusu!

Thanks a lot Very Happy

, I will try this tomorrow and let you know the updates soon.

Thanks & Regards,
_________________
-Hatim M P