Thursday, May 2, 2024

Converting fetched columns into an array

Coming up with a meaningful title for this post proved harder than normal. I was asked a question about how to fetch data, using a SQL Cursor, and convert some of the fetched columns into an array. The questioner had tried various approaches, but was always receiving an error in the SQL pre-compile step of his program.

The file in question contained 35 fields he was interested in. The record would be for an account, by year and month, 31 balance fields one for every day of the month (yes, I know not all months have 31 days), and finally a total monthly change balance. The questioner wanted to put all of the 31 day fields into one array.

Let me start with my version of his file, that I called TESTFILE:

01 A          R TESTFILER
02 A            ACCOUNTNBR    14P 0
03 A            YEAR           4P 0
04 A            MONTH          2P 0
05 A            BALANCE01     15P 2
06 A            BALANCE02     15P 2
07 A            BALANCE03     15P 2
08 A            BALANCE04     15P 2
09 A            BALANCE05     15P 2
10 A            BALANCE06     15P 2
11 A            BALANCE07     15P 2
12 A            BALANCE08     15P 2
13 A            BALANCE09     15P 2
14 A            BALANCE10     15P 2
15 A            BALANCE11     15P 2
16 A            BALANCE12     15P 2
17 A            BALANCE13     15P 2
18 A            BALANCE14     15P 2
19 A            BALANCE15     15P 2
20 A            BALANCE16     15P 2
21 A            BALANCE17     15P 2
22 A            BALANCE18     15P 2
23 A            BALANCE19     15P 2
24 A            BALANCE20     15P 2
25 A            BALANCE21     15P 2
26 A            BALANCE22     15P 2
27 A            BALANCE23     15P 2
28 A            BALANCE24     15P 2
29 A            BALANCE25     15P 2
30 A            BALANCE26     15P 2
31 A            BALANCE27     15P 2
32 A            BALANCE28     15P 2
33 A            BALANCE29     15P 2
34 A            BALANCE30     15P 2
35 A            BALANCE31     15P 2
36 A            MONTHLYCHG    15P 2

He said that he had many more fields in his file, but this is the minimum data set he needed. I am sure his file is keyed, but for my example I don't need a key.

In his version he was using a single row fetch from a SQL Cursor. It is more efficient to use a multiple row fetch from a Cursor, which is why I will be using that in my example program.

This is my example program:

01  **free
02  ctl-opt main(Main) option(*srcstmt) ;

03  dcl-proc Main ;
04    dcl-ds GotData extname('TESTFILE') qualified ;
05    end-ds ;

06    dcl-s Rows uns(10) inz(%elem(GotData : *max)) ;
07    dcl-s Counter like(Rows) ;

08    dcl-ds BalanceRow extname('TESTFILE') ;
09      BalanceArray packed(15:2) samepos(Balance01) dim(31) ;
10    end-ds ;

11    exec sql DECLARE C0 CURSOR FOR
                 SELECT * FROM TESTFILE
                    FOR READ ONLY ;

12    exec sql OPEN C0 ;

13    exec sql FETCH NEXT FROM C0 FOR :Rows INTO :GotData ;

14    exec sql CLOSE C0 ;

15    Rows = %elem(GotData) ;

16    for Counter = 1 to Rows ;
17      eval-corr BalanceRow = GotData(Counter) ;
18      dsply %char(BalanceArray(10)) ;
19    endfor ;
20  end-proc ;

Line 1: All of my code is now free format modern RPG. All of yours should be too.

Line 2: Using a Main procedure is more efficient as the RPG compiler does not include all the code necessary for the RPG cycle, even if I don't use it. And I have my favorite RPG control option I add to all my RPG programs.

Line 3: Start of the Main procedure, that ends at line 20.

Lines 4 and 5: This is the data structure array I will fetching the results from the Cursor into. The EXTNAME tells the compiler to use the file TESTFILE for the definition of the data structure's subfields. For a file with "hundreds of fields", as the questioner said his file had, this is the easy way to have all of the file's fields in the data structure without having to code them all manually into the program. As this is a data structure array it needs to be qualified, so that is used as the array's name. The array is an auto extending array, which means it will only contain the number of elements I move into it, up to 9,999.

Line 6: This is the definition of a variable that contains the maximum number of elements the GotData data structure array can contain.

Line 7: As its name suggests this variable will be used as a counter for a For-group.

Lines 8 – 10: This data structure is defined, with the EXTNAME, to have all the subfields that the file TESTFILE has for fields. On line 9 I am defining an array that will contain all of the balance fields in the file, 31 elements, and the SAMEPOS tells the compiler that this array starts at the first balance subfield.

Line 11: The definition of the Cursor I will be using to retrieve the data from TESTFILE.

Line 12: Open the Cursor.

Line 13: I fetch up to 9,999 rows from TESTFILE into the data structure array GotData.

Line 14: As I have all the data I need I close the Cursor.

Line 15: I could have used the SQL GET DIAGNOSITICS to return the number of rows I fetched from the Cursor. As I am using an auto extending array the number of elements in the array is the same as the number of rows of results I returned.

Lines 16 – 19: I am using a For-group to move the rows from the data structure array, GotData, into the data structure, BalanceRow, using the Eval corresponding operation code, EVAL-CORR. This means that only the subfields with the same names have their values moved to the BalanceRow data structure. On line 18 I display the value in the tenth element of the array. As I did not have the QUALIFIED keyword on Balance I can just use the array's name, BalanceArray, without needing to qualify it with the data structure's name.

As I mentioned above I added two records to TESTFILE. In the first I gave each balance field the same value as its name, i.e. the fields BALANCE10 contains 10.00 . In the second record all the balance fields contain 0.01 .

After compiling this program I started debug and added a breakpoint at line 18.

When I called the program and debug stopped at line 18 I could see what the array BalanceArray contains:

> EVAL balancearray
BALANCEARRAY OF BALANCEROW(1) = 0000000000001.00 
BALANCEARRAY OF BALANCEROW(2) = 0000000000002.00 
BALANCEARRAY OF BALANCEROW(3) = 0000000000003.00 
BALANCEARRAY OF BALANCEROW(4) = 0000000000004.00 
BALANCEARRAY OF BALANCEROW(5) = 0000000000005.00 
BALANCEARRAY OF BALANCEROW(6) = 0000000000006.00 
BALANCEARRAY OF BALANCEROW(7) = 0000000000007.00 
BALANCEARRAY OF BALANCEROW(8) = 0000000000008.00 
BALANCEARRAY OF BALANCEROW(9) = 0000000000009.00 
BALANCEARRAY OF BALANCEROW(10) = 0000000000010.00
BALANCEARRAY OF BALANCEROW(11) = 0000000000011.00
BALANCEARRAY OF BALANCEROW(12) = 0000000000012.00
BALANCEARRAY OF BALANCEROW(13) = 0000000000013.00
BALANCEARRAY OF BALANCEROW(14) = 0000000000014.00
BALANCEARRAY OF BALANCEROW(15) = 0000000000015.00
BALANCEARRAY OF BALANCEROW(16) = 0000000000016.00
BALANCEARRAY OF BALANCEROW(17) = 0000000000017.00
BALANCEARRAY OF BALANCEROW(18) = 0000000000018.00
BALANCEARRAY OF BALANCEROW(19) = 0000000000019.00
BALANCEARRAY OF BALANCEROW(20) = 0000000000020.00
BALANCEARRAY OF BALANCEROW(21) = 0000000000021.00
BALANCEARRAY OF BALANCEROW(22) = 0000000000022.00
BALANCEARRAY OF BALANCEROW(23) = 0000000000023.00
BALANCEARRAY OF BALANCEROW(24) = 0000000000024.00
BALANCEARRAY OF BALANCEROW(25) = 0000000000025.00
BALANCEARRAY OF BALANCEROW(26) = 0000000000026.00
BALANCEARRAY OF BALANCEROW(27) = 0000000000027.00
BALANCEARRAY OF BALANCEROW(28) = 0000000000028.00
BALANCEARRAY OF BALANCEROW(29) = 0000000000029.00
BALANCEARRAY OF BALANCEROW(30) = 0000000000030.00
BALANCEARRAY OF BALANCEROW(31) = 0000000000031.00

When the Display operation code, DSPLY, was reached the following was displayed:

DSPLY  10.00

Which is the same as what the field BALANCE10 contains.

The next time through the For-loop the following was displayed:

DSPLY  .01

As all of the balance fields in the second record contain 0.01 the value displayed is also what I expected.

The question has been answered in a simple and easy to understand way, K.I.S.S.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.

1 comment:

  1. Christophe SeewaldMay 3, 2024 at 4:11 AM

    Clever way to do it indeed.
    Good post as always Simon, thank you.

    Just one thing : you forgot the dim(*auto:9999) when declaring the GotData DS.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.