SQL Record Selection with Dynamic Lists
September 28, 2005 Ted Holt
SQL’s IN predicate provides an easily understood, practical way to select records (rows) of a database file (table) by comparing a field’s (column’s) value to a list of predetermined values. However, when using IN with a dynamic list, i.e., a list whose values and number of values are not specified until run time, IN has a drawback, namely that the programmer must allow for the number of values in the list in advance. This article will give you three ways to deal with dynamic lists.
But first, a brief review of the IN predicate is in order. One way to use IN is to specify a list of hard-coded literals. The following SQL query selects data for anyone whose last names are Jones, Doe, or Vine.
select cusnum,lstnam,init,city,state
from qiws/qcustcdt
where upper(lstnam) in ('JONES','DOE','VINE')
The query returns these rows.
CUSNUM LSTNAM INIT CITY STATE 839,283 Jones B D Clay NY 392,859 Vine S S Broton VT 475,938 Doe J W Sutter CA
A second form of IN uses a subquery to generate the list.
select cusnum,lstnam,init,city,state from qiws/qcustcdt where upper(lstnam) in (select name from qtemp/select)
The SQL processor reads the SELECT table in library QTEMP and builds a list from the values in the NAME column.
Let’s return to ways to deal with dynamic lists. Suppose you wish to allow the user to key one or more values of some type at run time. The user might key one, two, or a dozen values, and the keyed values are used to select records from a database file. It would be nice to place a varying number of host variables in a program, but each value requires its own host variable. The following embedded SQL command allows for 12 selection values.
C/exec sql C+ declare Input cursor for C+ select x.* from qcustcdt as x C+ where upper(lstnam) in C+ (:Nam01, :Nam02, :Nam03, :Nam04, C+ :Nam05, :Nam06, :Nam07, :Nam08, C+ :Nam09, :Nam10, :Nam11, :Nam12) C/end-exec
If it becomes necessary to allow for more values, more host variables must be added. If the user wishes to search for fewer than 12 names, the values in the unused host variables must not cause the search to return erroneous results. So, how can you deal with dynamic lists?
One method is to allow for a maximum number of host variables and fill the unused ones with a value that is unlikely to be stored in the database. Since a customer is unlikely to have the name !@#$%, for example, you might fill unused list elements with such a value, as in the following RPG program fragment, in which the user has entered 10 customer names.
C/exec sql C+ declare Input cursor for C+ select x.* from qcustcdt as x C+ where upper(lstnam) in C+ (:Nam01, :Nam02, :Nam03, :Nam04, C+ :Nam05, :Nam06, :Nam07, :Nam08, C+ :Nam09, :Nam10, :Nam11, :Nam12) C/end-exec C eval Nam11 = '!@#$%' C eval Nam12 = '!@#$%'
A second method is to use a sub query instead of a list of host variables. Create a temporary work file, into which the record selection values can be inserted. The following SQL query selects records customers whose names are Jones, Doe, and Vine.
create table qtemp/select (name char(12))
insert into qtemp/select values ('JONES')
insert into qtemp/select values ('DOE')
insert into qtemp/select values ('VINE')
select cusnum,lstnam,init,city,state
from qiws/qcustcdt
where upper(lstnam) in
(select name from qtemp/select)
Finally, a third method you can use is to create the list in a character string and use the LIKE predicate, rather than IN, to carry out record selection. The following RPG program accepts a list of last names in the second parameter.
* Note: QIWS must be in the library list at compile time
* and at run time.
Fqsysprt o f 132 printer
D*entry plist
D AAA111R pr extpgm('AAA111R')
D ouStatus 8a
D inList 120a const
D AAA111R pi
D ouStatus 8a
D inList 120a const
D AllOK c const('00-00000')
D HostStruc e ds extname(QCUSTCDT)
D List s 120a varying
D PssrIsActive s n
D SqlEof c const('02000')
D Status s like(ouStatus)
D True c const(*on)
C/exec sql
C+ set option closqlcsr=*endmod
C/end-exec
C/exec sql
C+ declare Input cursor for
C+ select x.* from qcustcdt as x
C+ where ','||:List||','
C+ like '%,'||trim(upper(lstnam))||',%'
C/end-exec
C
C eval *inlr = *on
C eval Status = AllOK
C eval List = %trim(inList)
C/exec sql
C+ open Input
C/end-exec
C if SQLStt >= SqlEof
C eval Status = '10-' + SqlStt
C exsr ShutDown
C endif
C
C dow '1'
C/exec sql
C+ fetch Input into :HostStruc
C/end-exec
C select
C when SqlStt = SqlEof
C leave
C when SQLStt > SqlEof
C eval Status = '20-' + SqlStt
C exsr ShutDown
C endsl
C except pline
C enddo
C
C exsr ShutDown
* =========================================================
C *pssr begsr
C
C eval *inlr = *on
C
C if PssrIsActive
C return
C endif
C
C eval PssrIsActive = true
C
C eval Status = '99-99999'
C exsr Shutdown
C
C endsr
C* =============================================
C ShutDown begsr
C
C eval *inlr = *on
C
C if Status <> AllOK
C dump(a)
C endif
C
C if %parms >= 1
C eval ouStatus = Status
C endif
C
C return
C
C endsr
Oqsysprt e pline 1
O lstnam
O init +0001
O cusnum +0001
O city +0001
O state +0001
The names must be separated by commas and there must be no embedded blanks in the list. Here are some examples of such lists.
JONES JONES,SMITH JONES,DOE,SMITH,GREEN,WHITE
If the user enters the names Doe and Vine, and the system retrieves a record for VINE, the WHERE clause resolves to this:
where ',DOE,VINE,' like '%,VINE,%'
The system selects the record.
|
On the other hand, suppose the system reads the record of customer Jones. The WHERE clause resolves to this:
where ',DOE,VINE,' like '%,JONES,%'
The system does not select the record.
Now you have three ways to use dynamic lists of unpredictable sizes in embedded SQL. Since selection from lists allows you to give the user powerful and flexible report and inquiry programs, these techniques are worth mastering.
