This is going to be a quickie. I was asked:

How can I get the 98th record from a file using SQL?

Fortunately this very simple, but first let me go through setting up my example.

I created a Db2 for i table to use in this example. I could have used a DDS file just as well. But this gives me an excuse to use one of what I consider one of the cool features of Db2 for i tables, identity columns.

If I am using a table with an identity column I don't have to increment some value in a field to keep a unique field in a file or table. The value for an identity columns are auto generated by Db2 for i, so all I have to do is just define them in my table. My example table just contains an identity column.

01 CREATE TABLE QTEMP.TESTFILE ( 02 RECORD_COUNT INTEGER GENERATED ALWAYS AS IDENTITY 03 (START WITH 1,INCREMENT BY 1,NOCYCLE) 04 )

The only column/field in the table/file is RECORD_COUNT . I have defined it as integer with no length attribute, when the table is created this becomes a 9 long binary field. I need the GENERATED ALWAYS AS IDENTITY as that is what tells the create table that this is an identity column. What I have on line 3 means that the identity column will start with 1, increment by 1, and when it reaches the maximum number it will not cycle back to 1. That is not going to be an issue for this file.

So let me fill this table…

01 **free 02 ctl-opt option(*nodebugio:*srcstmt) ; 03 dcl-s Count int(10) ; 04 exec sql SET OPTION COMMIT = *NONE, CLOSQLCSR = *ENDMOD ; 05 exec sql DELETE FROM QTEMP.TESTFILE ; 06 exec sql ALTER TABLE QTEMP.TESTFILE 07 ALTER COLUMN RECORD_COUNT RESTART WITH 1 ; 08 for Count = 1 to 100 ; 09 exec sql INSERT INTO QTEMP.TESTFILE 10 (RECORD_COUNT) 11 VALUES(DEFAULT) ; 12 endfor ;

Line 1: I only use totally free RPG as I find it so easy to use, compared to fixed column definitions, etc.

Line 2: My favorite control options make it easier to debug any program.

Line 3: I have defined this variable to be used when I get the 98th row/record.

Line 4: I always like to put these SQL options in my source member to make sure they are not forgot when this program is compiled.

Line 5: SQL's DELETE FROM will delete all the rows/records from the table/file. Not an issue the first time the program is run, but it is run more than once I want to start with an empty table/file.

Line 6 and 7: The identity column in a table/file is not reset when the all the records from the file is deleted, it will continue incrementing from the previous value it used. I want to reset the identity column back to its initial value, 1.

Line 8 – 12: I need to insert 100 rows/records into the example table/file. Alas I could not find a way to insert 100 rows in one statement. Therefore, I have this in a For group to be performed 100 times.

Lines 9 – 11: I considered there are two ways I would have coded the insert statement. The way I have shown above gives the name of the column/field that is being insert into. In my opinion this is redundant as I am inserting all the columns/fields in the table/file so for myself I would have coded this as below, without line 10.

09 exec sql INSERT INTO QTEMP.TESTFILE 11 VALUES(DEFAULT) ;

By using DEFAULT I am inserting the default value into the only column/field in the file. As this is an identity column Db2 for i places the next incremental value in the column/field for me.

The next part is where I get the 98th row/record.

13 exec sql SELECT RECORD_COUNT INTO :Count 14 FROM QTEMP.TESTFILE 15 ORDER BY RECORD_COUNT 16 OFFSET 97 ROWS 17 FETCH FIRST ROW ONLY ; 18 dsply ('Record_Count = ' + %char(Count)) ; 19 *inlr = *on ;

Lines 13 – 17: This is where the important part happens. The crucial parts of this statement are on lines 16 and 17. The OFFSET , is almost like RPG's SETGT , it tells the statement where to start the select. I need to offset 97 rows as I need the next one, the 98th. The FETCH FIRST ROW ONLY gets the next column/row, the 98th. I used the ORDER BY , line 15, just to show that you might need to sort your table/file to get the 98th record you want. The value of RECORD_COUNT is placed in the program variable Count .

Line 18: The value in the variable Count is displayed to me.

DSPLY Record_Count = 98

If I had not reset the identity column, lines 6 and 7, the second time I ran this program I would have seen:

DSPLY Record_Count = 198

Third time the value would have been 298, etc. This is why I reset the identity column.

Thanks to the offset in the Select statement getting the 98th, or whatever row number you desire, was an easy thing to do.