Using values in an array for the SQL Where clause

It was a good question to ask: "Is there a way to use an array in the Where clause of a SQL Select statement?"

I am disappointed that it is not possible to use an array in that manner:

01 exec sql SELECT * FROM PERSON 02 WHERE LAST_NAME IN (:MyArray) ;

This code will not pass the SQL precompile. It gives the following error message in the precompile listing:

MSG ID SEV RECORD TEXT SQL0312 30 13 Position 32 Variable MYARRAY not defined or not usable for reason code 2.

When I look up that message I see that arrays are not allowed:

Message ID . . . . . . . . . : SQL0312 Message file . . . . . . . . : QSQLMSG Library . . . . . . . . . : QSYS Message . . . . : Variable &1 not defined or not usable for reason code &2. Cause . . . . . : The variable &1 appears in the SQL statement, but is not usable for reason code &2: 2 -- The variable is a dimensioned array.

So what are my alternatives. I could think of two. The first involves writing the contents of the array to a file or table, and then using a statement with a subselect in the Where clause like this:

01 exec sql DECLARE C0 CURSOR FOR 02 SELECT * FROM PERSON 03 WHERE LNAME IN (SELECT * FROM QTEMP.TEMPFILE) 04 FOR READ ONLY ;

I decided on a simpler approach by creating a SQL statement in a variable and then use the Prepare statement. I decided that this approach made the most sense in the scenario I had. The full program for this method is:

01 **free 02 ctl-opt option(*srcstmt) ; 03 dcl-s MyArray varchar(30) dim(*auto : 9999) ; 04 dcl-ds Data extname('PERSON') qualified dim(*auto : 9999) ; 05 end-ds ; 06 dcl-s Rows int(5) inz(%elem(Data : *max)) ; 07 dcl-s Separator char(3) inz(''',''') ; 08 dcl-s String varchar(2048) ; 09 exec sql SET OPTION DATFMT = *ISO ; 10 MyArray = %list('ALLEN' : 'BOND' : 'MELLOR') ; 11 String = 'SELECT * FROM PERSON WHERE LNAME IN (''' + 12 %concatarr(Separator : MyArray) + 13 ''') FOR READ ONLY' ; 14 exec sql PREPARE S0 FROM :String ; 15 exec sql DECLARE C0 CURSOR FOR S0 ; 16 exec sql OPEN C0 ; 17 exec sql FETCH C0 FOR :Rows INTO :Data ; 18 exec sql CLOSE C0 ; 19 dsply ('No. of rows in Data = ' + %char(%elem(Data))) ; 20 *inlr = *on ;

Line 1: In the 2020s all code should be totally free RPG.

Line 2: My favorite control option makes it easy to find where the program errors.

Line 3: Definition for my array, unsurprisingly called MyArray. The *AUTO denotes that is an auto-extending array, up to 9,999 elements.

Lines 4 and 5: Definition of the Data Structure array that will contain the results when the data is Fetched from the Cursor.

Line 6: Definition of a variable to contain the maximum number of elements that the Data Structure array has.

Line 7: I have defined this variable to contain the characters needed to place '','' between each array element.

Line 8: This will contain the Select statement that will be used to define the Cursor.

Line 9: I have a date column in the Table I have found that if I do not set the Set option statement to set the date format the program will not compile.

Line 10: I am loading my array with these last names using the %LIST built in function, BiF.

Lines 11 – 13: This is where I concatenate my SQL statement together.

Line 11: The start of the SQL statement.

Line 12: The Concatenation Array Bif, %CONCATARR, makes this so easy. The first parameter is the separator characters I want to use. I got so confused with trying to work out the right number of apostrophes ( ' ) I created and used this variable instead. The second parameter is the array. As this is an auto-extending array I don't have to worry about it concatenating unused array elements into the string.

Line 13: Is the last part of the SQL statement.

Line 14: The Prepare statement takes the RPG variable String and converts it into something that SQL can use, S0.

Line 15: I now can declare the cursor using S0.

Line 16: Open the cursor.

Line 17: Fetch up to 9,999 rows into the Data Structure array. I know I will only return three results.

Line 18: Close the cursor.

Line 19: Display the number of array elements, rows of data, that were fetched from the cursor.

After compiling the program I place a breakpoint at line 14 so I can check that the SQL statement that has been built using the %CONCATARR is what I expect. When the program is run it stops at line 14 and I can check the contents of the variable String:

> EVAL String STRING = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'SELECT * FROM PERSON WHERE LNAME IN ('ALLEN','BOND','MELLOR'' 61 ') FOR READ ONLY ' 121 ' '

This looks good to me. Pressed F12 to continue. At the end of the program the following was displayed:

DSPLY No. of rows in Data = 3

This is correct as there are only three people with those last names.

As I said I cannot use an array in the Where clause, but I have shown how simple it is to convert the contents of the array into a string that I can then use.

 

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