Guru: SELECT INTO And Arrays
March 8, 2021 Ted Holt
I got egg on my face again. I told a couple of colleagues that they could use the SELECT INTO statement to load multiple rows into an array data structure in an RPG program. Boy, was I wrong! I had confused SELECT INTO with the FETCH statement, of course, which retrieves data over which a cursor has been declared.
But the matter continued to nag me. I much like the simplicity of SELECT INTO. There’s no cursor to declare, open, fetch from and close, the same reason I like the FOR loop in SQL PL. It seemed (and continues to seem) unjust to me that I can retrieve one data value into a scalar variable without a cursor, but I can’t put two or more values into an array without a cursor. What’s so hard about putting the first row into the first element, the second row into the second element, and so on? A caveman could do it.
Well, as the old saying goes, where there’s a will, there’s a lazy programmer. It turns out it’s possible to load an array with SELECT INTO. You just have to be sneaky about it.
To illustrate, I’ll use physical file QCUSTCDT, which you can find on your system in the QIWS library. Let’s say we need the account numbers of the customers who owe us at least 100 units of currency. A simple SELECT yields one row per customer. We can use the LISTAGG function to combine all the customer account numbers into one long string. Since the account number is zoned decimal, the DIGITS function converts it to character data.
select listagg(digits(c.CusNum)) within group (order by c.CusNum) as List from qcustcdt as c where c.BalDue >= 100;
LISTAGG converts a set of rows into a single value. The first parameter is an expression that specifies which data is to be retrieved. The second parameter is the value to separate the values. I omitted the second parameter since I didn’t want any separators. If I had specified the second parameter, I would have used two single quotes with nothing between them, like this.
Sorting works a little differently. Here the ORDER BY is placed into the WITHIN GROUP clause so that LISTAGG can take care of sorting the data. In this example, data structure CustInfo defines the same portion of memory two ways — as scalar value LIST and as array ACCOUNT.
Here’s the result set:
How do we turn that into in array? With a data structure!
**free dcl-ds CustInfo qualified; List char(78) pos(1); Account char( 6) dim(13) pos(1); end-ds CustInfo; dcl-s CX uns( 5); exec sql select listagg(digits(c.cusnum),'') within group (order by c.cusnum) as List into :CustInfo.List from qcustcdt as c where c.BalDue >= 100; CX = 1; dow CustInfo.Account (CX) <> *blanks; dsply CustInfo.Account (CX); CX += 1; enddo;
Here’s another example. Let’s get a list of all states in which we have customers.
select listagg(distinct c.State,'') within group (order by c.State) as List from qcustcdt as c;
I included this example for one reason only: to show you that you can use the DISTINCT keyword to eliminate duplicate values from the returned result.
You don’t have to put the results into an array of fixed-length values. For instance, you might prefer the values to be separated by a comma or some other value. Specify the separator string in the second parameter of LISTAGG. This statement . . .
select listagg(distinct trim(c.City) concat ',' concat trim(c.State),';') within group (order by c.City, c.State) as List from qcustcdt as c;
. . . yields this string . . .
This technique would be handy in cases where the retrieved values were of different lengths, i.e. VARCHAR columns or TRIM’ed data. You can use RPG’s %SCAN function within a loop to extract the values.
So far I’ve only used this technique in my work to retrieve one column (field). I wondered if this technique would work for a multiple-column result set. It turns out it will. Here’s one last illustration.
dcl-ds CustInfo qualified; List char(512) pos(1); Record dim(13) pos(1); Account zoned( 6) overlay (Record: 1); LastName char ( 8) overlay (Record: *next); City char ( 6) overlay (Record: *next); State char ( 2) overlay (Record: *next); end-ds CustInfo; dcl-s CX uns( 5); exec sql select listagg(digits(c.CusNum) concat c.LstNam concat c.City concat c.State) within group (order by c.State, c.LstNam) as List into :CustInfo.List from qcustcdt as c where c.BalDue >= 100; CX = 1; dow CustInfo.Record (CX) <> *blanks; dsply CustInfo.Record (CX); dsply CustInfo.LastName (CX); CX += 1; enddo;
It turned out to be a simple matter of concatenating the values of four fields in LISTAGG and defining the four fields in the data structure.
There you have it — a multi-row SELECT INTO into an array — and I was told that it couldn’t be done.