Guru: Continue A Sequence When Inserting
January 15, 2018 Ted Holt
Carlos writes, “Hey, Ted! I have a question regarding inserting rows into a file that is keyed on a sequence number. I need to insert more rows, and I need the new rows to have the next available sequence numbers. Can I perform this task with an SQL INSERT statement, or do I have to use record-level access?”
If the sequence number were defined as an identity column, Carlos would have no problem. Unfortunately, the sequence number column is a simple numeric field. Fortunately, I was able to give Carlos two solutions. It would not surprise me if you can suggest others.
Let’s set up the problem. The table (physical file) into which Carlos wants to insert the new rows — let’s call it FILEA — looks something like this:
The table with the new data — FILEB — looks like this:
Carlos wanted to insert three new rows beginning with sequence number 6. The easy way to do this is to use a function I wrote a long time ago — the INCR function. It’s a simple RPG routine that returns an integer value that is one greater than the previous value.
insert into filea select incr(5), field3 from fileb order by datab
Now FILEA looks like this:
If you don’t want to use my function, you can use IBM’s code instead. Create a sequence. I’ll unimaginatively call it SEQ1.
create or replace sequence mylib.seq1 as dec (3) start with 6
Use the NEXT VALUE expression to retrieve the next sequence number from SEQ1.
insert into filea select next value for seq1, datab from fileb order by datab
If it were not for one little-bitty limitation of CREATE SEQUENCE and ALTER SEQUENCE, I would not have mentioned my INCR function. That limitation is this: the START WITH value must be a numeric literal. I tried to retrieve the largest sequence number from FILEA into a variable and set the sequence to the next integer value, but no soap. However, I was able to make it work with INCR. Here’s a dynamic compound statement that worked properly.
begin declare NextSeq integer; values (select max(field1) from filea) into NextSeq; insert into filea select incr(NextSeq), datab from fileb order by datab; end
I’m very grateful for the functionality that IBM packs into the world’s best relational database management system. I’m also grateful that I can write my own code when necessary. We have the best of both worlds.