Another way of using an array for the SQL Where clause

Earlier I wrote a post about how to take data from an array and use it in a SQL Where clause. The method I described was to concatenate the elements of the array into a string, and then use that in the Where clause.

I did mention I had tried an alternative method moving data from the array into a Table and then using it as a subselect in the Where clause. I have been asked several times how I did that, which is what this post is about. Let me state here that what I am going to describe here is not as efficient as the method I described before, which is what you should use.

This alternative program consists of the main body, and two subprocedures. I am going to start by showing the main body:

01 **free 02 ctl-opt option(*srcstmt) dftactgrp(*no) ; 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) ; 07 exec sql SET OPTION DATFMT = *ISO ; 08 MyArray = %list('ALLEN' : 'BOND' : 'MELLOR') ; 09 MakeArrayFile() ; 10 GetData() ; 11 dsply ('No. of rows in Data = ' + %char(%elem(Data))) ; 12 *inlr = *on ;

Line 1: Totally free RPG is not new, its seven year old. If you are not using it you really need to take the time to learn it before you get left behind by everyone else.

Line 2: This is my favorite control option I add to all of my programs. It makes it so much easier to find where the program error has occurred.

Line 3: Definition for my array, MyArray. It is an auto-extending array, hence the *AUTO in the DIM keyword, and can contains up to 9,999 elements.

Lines 4 and 5: This is the definition of the Data Structure array that will contain the results when I fetch the data from the Cursor.

Line 6: I have defined this variable to be used whenever I need to have a count of elements in the Data Structure array or rows in the results.

Line 7: I have found that if I have a date column in the Table I am fetching data from I need use the Set option statement to define the format of that date, otherwise the program will not compile.

Line 8: I load the array with these last names using the %LIST built in function, BiF.

Line 9: I call the first subprocedure to make the Table I will be using in the subselect.

Line 10: And then the subprocedure to fetch the data from Cursor.

Line 11: Before the program ends I will display the number of elements in the Data Structure array, as that is the same as the number of rows I fetched from the PERSON Table.

Onto the first subprocedure, this is where the data from the elements of the array and convert into rows in a Table.

13 dcl-proc MakeArrayFile ; 14 dcl-ds MyDsArray qualified dim(*auto : 9999) ; 15 X char(30) ; // Name is unimportant 16 end-ds ; 17 exec sql CREATE OR REPLACE TABLE QTEMP.TEMPFILE AS 18 (SELECT LNAME FROM PERSON) 19 DEFINITION ONLY ON REPLACE DELETE ROWS ; 20 MyDsArray = MyArray ; 21 Rows = %elem(MyDsArray) ; 22 exec sql INSERT INTO QTEMP.TEMPFILE :Rows ROWS VALUES(:MyDsArray) ; 23 end-proc ;

Lines 14 – 16: I need to Data Structure array to be able to insert data from an array's elements into a Table. Again this is an auto-extending array. I have to define a subfield for a Data Structure array, and for this one I have given the subfield the name X, its name is unimportant and could be anything.

Lines 17 – 19: This is where I create the output Table that will contain the values from the original array. I have used CREATE OR REPLACE TABLE as if there is already a Table of this name in QTEMP the statement will replace it and delete its data, which is shown by what is on line 19.

Line 18: This SQL statement defines the Table that is created. Here the Table will contain one column which is the same as the Last Name column in the PERSON table.

Line 19: As I only want to define the Table, and not fill it with data, I must use DEFINITION ONLY.

Line 20: A multiple row Insert must use a Data Structure array, and this statement moves the contents of MyArray into the Data Structure array I defined earlier in this subprocedure.

Line 21: I need to know how many rows of data I will be Inserting into the Table. I calculate that from the number of elements in the Data Structure array. As it is an auto-extending array it contains three rows.

Line 22: Here I perform the multiple row insert that takes the values from each element in the array and creates a row for each one in the output file for them.

The last subprocedure gets the matching data from the PERSON table.

24 dcl-proc GetData ; 25 Rows = %elem(Data : *max) ; 26 exec sql DECLARE C0 CURSOR FOR 27 SELECT * FROM PERSON 28 WHERE LNAME IN (SELECT * FROM QTEMP.TEMPFILE) 29 FOR READ ONLY ; 30 exec sql OPEN C0 ; 31 exec sql FETCH C0 FOR :Rows ROWS INTO :Data ; 32 exec sql CLOSE C0 ; 33 end-proc ;

Line 25: Set the variable Rows to contain the maximum number of elements that the Data Structure array Data can hold, which is 9,999.

Lines 26 – 29: Definition of the Cursor I will be using the fetch matching rows from PERSON.

Line 28: The Where clause says that the Last Name from PERSON must be found in the Table I created from the array to be fetched.

Line 30: Open the Cursor.

Line 31: Fetch the data, and insert it into the Data Structure array Data.

Line 32. Close the Cursor.

I compile this program. When I call it the following is displayed:

DSPLY No. of rows in Data = 3

Which shows that only the three rows I wanted were retrieved from PERSON.

I will repeat this is not the preferred, or efficient, method of using data from an array in the Where clause.

 

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