|
Numbering Rows in an SQL Result Set
by 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.
|
PRODATA COMPUTER SVCS
