CURSOR WITH RETURN AND UPDATE?
in [DB2]
|
Prev: Have you registered yet for Episodes 2 & 3 of The DB2Night Show on Oct 1 and 15th
Next: DB2 position function (again!)
From: jefftyzzer on 28 Sep 2009 19:27 On Sep 28, 11:30 am, esmith2112 <esmith2...(a)gmail.com> wrote: > On Sep 28, 2:00 pm, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote: > > > > > On Sep 28, 9:41 am, esmith2112 <esmith2...(a)gmail.com> wrote: > > > Why do you need a cursor at all? Why not just do > > > update mytable > > set sent_timestamp = current timestamp > > where sent_timestamp is null; > > > and then > > > open curs1; > > > If you really do need to loop, I think you're better off using a > > cursor FOR loop (much faster than open...while...fetch), like so: > > > FOR l_mytab as c_mytab cursor for > > select t_id, t_name > > from mytable > > where sent_timestamp is null > > > DO > > update mytable > > set sent_timestamp = current timestamp > > where t_id = l_mytab.id;-- > > > end for;-- > > > As to returning the cursor, the easiest thing to do would be to simply > > declare it as before and open it after the update, i.e., put the "open > > curs1;" after the "end for;" > > > For something even crazier (and likely faster), you could do this: > > > declare curs1 cursor with return for > > select t_id, t_name > > from > > final table > > (update mytable > > set sent_timestamp = current timestamp > > where sent_timestamp is null > > );-- > > > open curs1;-- > > > --Jeff > > Thanks, Jeff. I'll give your suggestions a shot. I haven't done > application development in a long while, so my skills are definitely > rusty. I knew I was probably missing a fundamental concept here. No problem--we're all still learning. I've got no issues with cursors (some of my best friends....), I just tend to prefer cursor FOR loops over the "old-fashioned" kind. Regards, --Jeff
From: esmith2112 on 29 Sep 2009 08:48 > > No problem--we're all still learning. I've got no issues with cursors > (some of my best friends....), I just tend to prefer cursor FOR loops > over the "old-fashioned" kind. > > Regards, > > --Jeff Couldn't get the FOR to work like I wanted for me. I really wanted the "pre-update" values, but ended up getting the post-update values back. But I did make some progress using your other technique--slightly modified. declare curs1 cursor with return for select * from OLD TABLE ( update mytable set sent_timestamp = current timestamp, stat_id = 'I' where sent_timestamp is null ) As I wanted the "pre" image of the table, it gave me only those rows which qualified for the performed update. The new requirement is to return the the same updated rows and also those rows from the same table that were sent more than 5 minutes ago and have a stat_cd value of "I" (which are the two columns that just got updated). These seemed like a piece of cake at this point so I tried doing a simple UNION operation like this: declare curs1 cursor with return for select * from OLD TABLE ( update mytable set sent_timestamp = current timestamp, stat_id = 'I' where sent_timestamp is null ) union all select * from mytable where stat_cd = 'I' and current timestamp > sent_timestamp + 5 minutes The compiler doesn't like this. I get: SQL20165N An SQL data change statement within a FROM clause is not allowed in the context in which it was specified. This hearkens back to my original problem where I tried to open the simple cursor (with return) and then perform the update. The engine complains that it can't do an update on a pending open cursor which hasn't been closed. (I forget the exact error.) So am I out of luck here? Evan
From: jefftyzzer on 29 Sep 2009 14:08 On Sep 29, 5:48 am, esmith2112 <esmith2...(a)gmail.com> wrote: > > No problem--we're all still learning. I've got no issues with cursors > > (some of my best friends....), I just tend to prefer cursor FOR loops > > over the "old-fashioned" kind. > > > Regards, > > > --Jeff > > Couldn't get the FOR to work like I wanted for me. I really wanted the > "pre-update" values, but ended up getting the post-update values back. > But I did make some progress using your other technique--slightly > modified. > > declare curs1 cursor with return for > select * > from OLD TABLE ( > update mytable > set sent_timestamp = current timestamp, > stat_id = 'I' > where sent_timestamp is null > ) > > As I wanted the "pre" image of the table, it gave me only those rows > which qualified for the performed update. > > The new requirement is to return the the same updated rows and also > those rows from the same table that were sent more than 5 minutes ago > and have a stat_cd value of "I" (which are the two columns that just > got updated). These seemed like a piece of cake at this point so I > tried doing a simple UNION operation like this: > > declare curs1 cursor with return for > select * > from OLD TABLE ( > update mytable > set sent_timestamp = current timestamp, > stat_id = 'I' > where sent_timestamp is null > ) > > union all > > select * from mytable > where stat_cd = 'I' and current timestamp > sent_timestamp + 5 > minutes > > The compiler doesn't like this. I get: > SQL20165N An SQL data change statement within a FROM clause is > not allowed in the context in which it was specified. > > This hearkens back to my original problem where I tried to open the > simple cursor (with return) and then perform the update. The engine > complains that it can't do an update on a pending open cursor which > hasn't been closed. (I forget the exact error.) > > So am I out of luck here? > > Evan This may be an occasion where you need to use a common table expression. I've not tested this, but my thinking is you might need something like: declare curs1 cursor with return for with mytab1 (optional column list) as ( select * from OLD TABLE ( update mytable set sent_timestamp = current timestamp, stat_id = 'I' where sent_timestamp is null ) ), mytab2 (optional column list) as ( select * from mytable where stat_cd = 'I' and current timestamp > sent_timestamp + 5 minutes ) select * from mytab1 UNION ALL select * from mytab2; --Jeff
From: esmith2112 on 30 Sep 2009 09:27
> > This may be an occasion where you need to use a common table > expression. I've not tested this, but my thinking is you might need > something like: > > declare curs1 cursor with return for > with > mytab1 (optional column list) as > ( > select * > from OLD TABLE ( > update mytable > set sent_timestamp = current timestamp, > stat_id = 'I' > where sent_timestamp is null > ) > ), > mytab2 (optional column list) as > ( > select * from mytable > where stat_cd = 'I' and current timestamp > sent_timestamp + 5 > minutes > ) > select * from mytab1 UNION ALL select * from mytab2; > > --Jeff BINGO! Thanks for all your input. I've learned something new. Have a great day, Evan |