Pages

Wednesday, November 29, 2023

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.

4 comments:

  1. I have used the following method in several programs, when I have an array of 10 entries or less.

    dcl-ds *n;
    MyArray char(10) dim(5) ;
    e1 char(10) pos(1);
    e2 char(10) pos(11);
    e3 char(10) pos(21);
    e4 char(10) pos(31);
    e5 char(10) pos(41);
    end-ds ;

    MyArray = %list('ALLEN' : 'BOND' : 'MELLOR': ' ' : ' ') ;

    Exec SQL SELECT * FROM PERSON WHERE LNAME IN (:e1, :e2, :e3, :e4, :e5)
    FOR READ ONLY' ;

    ReplyDelete
  2. There is another way. SYSTOOLS.SPLIT() is an SQL table function that takes a single string, and splits it by a delimiter character into row data.
    You can use %CONCATARR to create a CSV from your array, pass that to SPLIT(), then inner join to the PERSON table.

    listOfNames = %CONCATARR(',':myArray);

    EXEC SQL
    DECLARE C0 CURSOR FOR
    SELECT P.* FROM PERSON P
    INNER JOIN TABLE(SYSTOOLS.SPLIT(:LISTOFNAMES,',')) S
    ON S.ELEMENT = P.LNAME
    FOR READ ONLY;

    You still can't use the array directly, but at least you don't have to build your SQL statement as a string. This works in 7.4 and 7.5

    ReplyDelete
  3. In my tests, the SQL demonstrator shows a CPU time of 764ms for SYSTOOLS.SPLIT. But if you replace JOIN with WHERE P.LNAME IN (SELECT ELEMENT FROM TABLE(SYSTOOLS.SPLIT(:LISTOFNAMES,','))) then the CPU time for this function immediately becomes 2ms

    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.