Numbering Rows in an SQL Result Set
December 14, 2005 Ted Holt
The code for this article is available for download.
As I said on in an issue a few weeks ago, I like functions a lot. In this article, I present an SQL scalar function I wrote a few years ago that has proven itself to be handy. Consider it a Christmas present from Four Hundred Guru. I ho-ho-hope you like it.
I call the function INCR, which is short for “increment.” I wrote it as a way to number the rows returned by an SQL SELECT query. INCR takes one parameter, a number that is one less than the first sequential number to be returned. If I had to do it over again, I would probably assign the starting value to the parameter, but I don’t have a compelling reason to rewrite working code and I would prefer to give you a version that is working than one that I haven’t used in production.
In the following example, a customer file is sorted on last name and initials. The rows are numbered beginning with 1.
SELECT incr(0),
LSTNAM, INIT, CUSNUM, CITY, STATE, ZIPCOD
FROM qiws/qcustcdt
ORDER BY 2,3
Here’s the output from the query.
INCR ( 0 ) LSTNAM INIT CUSNUM CITY STATE ZIPCOD
1 Abraham M T 583,990 Isle MN 56,342
2 Alison J S 846,283 Isle MN 56,342
3 Doe J W 475,938 Sutter CA 95,685
4 Henning G K 938,472 Dallas TX 75,217
5 Johnson J A 938,485 Helen GA 30,545
6 Jones B D 839,283 Clay NY 13,041
7 Lee F L 192,837 Hector NY 14,841
8 Stevens K L 389,572 Denver CO 80,226
9 Thomas A N 693,829 Casper WY 82,609
10 Tyron W E 397,267 Hector NY 14,841
11 Vine S S 392,859 Broton VT 5,046
12 Williams E D 593,029 Dallas TX 75,218
Here’s the same example, but the first row is numbered 10,000 instead of 1.
SELECT incr(9999),
LSTNAM, INIT, CUSNUM, CITY, STATE, ZIPCOD
FROM qiws/qcustcdt
ORDER BY 2,3
INCR ( 9999 ) LSTNAM INIT CUSNUM CITY STATE ZIPCOD
10,000 Abraham M T 583,990 Isle MN 56,342
10,001 Alison J S 846,283 Isle MN 56,342
10,002 Doe J W 475,938 Sutter CA 95,685
10,003 Henning G K 938,472 Dallas TX 75,217
10,004 Johnson J A 938,485 Helen GA 30,545
10,005 Jones B D 839,283 Clay NY 13,041
10,006 Lee F L 192,837 Hector NY 14,841
10,007 Stevens K L 389,572 Denver CO 80,226
10,008 Thomas A N 693,829 Casper WY 82,609
10,009 Tyron W E 397,267 Hector NY 14,841
10,010 Vine S S 392,859 Broton VT 5,046
10,011 Williams E D 593,029 Dallas TX 75,218
Here’s the the source code for the RPG program that increments the INCR function. I also call it INCR. Be sure to move the code right five places when you store the source code in a source physical file, or download this version.
* ================================================================= * Source code for the SQL INCR user-defined function. * ================================================================= * On each call, this function returns one more than on the previous * call. The function should be invoked with a parameter value one * less than than the first desired return value. * ================================================================= * To create function in SQL: * * create function MYLIB/incr * (counter integer) * returns integer * external * called on null input * not deterministic * language rpgle * no sql * parameter style db2sql * scratchpad 4 * final call * disallow parallel * ================================================================= * To use in SQL: * select xxx, incr(0), xxx from xxx -- begin at 1 * select xxx, incr(999), xxx from xxx -- begin at 1000 H dftactgrp(*no) actgrp(*caller) D SeedValue s 10i 0 D ReturnValue s 10i 0 D SeedNull s 5i 0 D ReturnNull s 5i 0 D SQLState s 5a D FunctionName s 517a D SpecificName s 128a D MsgText s 70a varying D Counter s 10i 0 D FinalCall s 10i 0 D FirstCall c const(-1) D NormalCall c const(0) D LastCall c const(1) D D NullValue c const(-1) D NotNullValue c const(0) C *entry plist C parm SeedValue C parm ReturnValue C parm SeedNull C parm ReturnNull C* DB2SQL style parms C parm SQLState C parm FunctionName C parm SpecificName C parm MsgText C* Scratchpad C parm Counter C parm FinalCall C* if first call, and seed is not null, initialize to seed C if FinalCall = FirstCall C if SeedNull <> NullValue C eval Counter = SeedValue C else C eval Counter = *zero C endif C endif C * process normal call C if FinalCall <> LastCall C eval Counter = Counter + 1 C eval ReturnValue = Counter C else C eval *inlr = *on C endif C C eval ReturnNull = NotNullValue C eval SQLState = '00000' C return * =========================================================== C *pssr begsr C C eval SQLState = '38E01' C eval MsgText = 'RPG program error -- INCR.' C return C C endsr
Once you have the source on your system compile it using the Create Bound RPG (CRTBNDRPG) command. I suggest you compile it to run in the caller’s activation group. I’ve already set the activation group information in the H spec. Change it if you want something different.
|
Then run the SQL CREATE FUNCTION command you’ll find in the comments of the RPG program.
create function MYLIB/incr
(counter integer)
returns integer
external
called on null input
not deterministic
language rpgle
no sql
parameter style db2sql
scratchpad 4
final call
disallow parallel
I installed this function on the production system at my day job when I needed to load a file that required a unique value in a numeric key field. Without the INCR function, I would have had to use native I/O, rather than SQL, to load the file.
INCR relies on a scratchpad to remember the value of the series from one row to the next. For more information and examples of use of the scratchpad, see the related articles.
