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.
While the OFFSET is great, I believe this solution gives the 98th record in the query, not the 98th record in the file. Is there are reason not to use something like this?
ReplyDeleteSELECT * FROM QTEMP.TESTFILE WHERE RRN(QTEMP.TESTFILE) = 98
in my example the Select statement would also work.
DeleteBut in the real world if there are any deleted rows/records before the 98th row, then by using RRN I would get a "false positive" (unless the 98th is also deleted too).
Select fld1,fld2,fld3 into :ds from qtemp.testfile where record_count = 98
ReplyDeleteWhy can't we hardcode 98 directly ?
And thanks for explaining about offset keyword
In this simple example you are correct, your statement would work.
DeleteIn the real world some of the rows before the 98th might have been deleted, or the 98th itself. By just selecting where the identity column = 98 would not return the 98th row.
Thank you Simon for great tip. I wouldn't have opened this blog if it weren't you (assuming RRN(Col) might have been used). Its worthy opening your blog, glad to know wealthy tip. Till date I was goi g by native just to mitigate record deletions. Now, I can go by SQL without worries :)
ReplyDeleteI think Limit and offset is the ideal approach for paging and retrieving specific RRN record.
ReplyDeleteI think what we have an issue here with connotation. To me, ROW connotes a specific record in a data file. Because of that, I got hung up on the traditional view of a file. With so many ways to configure and access our data, that concept is almost outdated. To free myself, I could frame this requirement as the XXth record in a data set. (Yes, I started professional life as an English teacher.)
ReplyDelete