|
Alternate SQL Row-Selection Criteria, Take 3
Published: January 11, 2006
by Ted Holt
Two previous issues of this newsletter have presented ways to put two or more record-selection alternatives into one SQL query. I use these techniques often because they reduce or eliminate the need to clone programs. I recently learned another way to implement alternate record-selection criteria and would like to share it with you today.
Let's begin with an example table of people.
create table mylib/people
( FirstName char(12),
LastName char(12),
Street char(20),
City char(16))
Here's the data I placed into my copy of the PEOPLE table.
FIRSTNAME LASTNAME STREET CITY
========= =========== ================= ============
Smith Whiteside 451 Elom Street Lost Angeles
Joe Smith 3820 N. 1536th St New Yolk
Tom Jones 48 Smith St. Weeno
Ron Smitherman 121 Mapel St Last Vegas
Billy Blacksmith 3801 Oke St. Weeno
Pete Smithson 20 Mane St. Jaxon
William Jefferson 2001 S. North St. Smithport
Abdul Wong-Garcia 4949 E. West St. Bridgesmith
Sally Smythe 899 Goldsmith Ave Hotlanta
In some societies, it is not uncommon for one person's given name to be someone else's surname. My own surname, Holt, is such a name. Suppose a user needs to be able to search for a value in either the first name field or the last name field. How might that search be coded in SQL?
The new technique I have recently learned is to use the CASE construct to select among fields at runtime. Look at the following cursor declaration.
declare Input cursor for
select Firstname, LastName, Street, City
from people
where (case when :Select = 'F' then FirstName
when :Select = 'L' then LastName
end) = :SearchString
This example includes two host variables--Select and SearchString. The user would load these values before the query executes. If host variable Select has a value of F, SQL compares the FirstName field to the value in host variable SearchString. If Select has a value of L, SQL searches the LastName field instead.
If Select is F and SearchString is Smith, the user gets the following result set.
FIRSTNAME LASTNAME STREET CITY
========= =========== ================= ============
Smith Whiteside 451 Elom Street Lost Angeles
If Select is L and SearchString is Smith, the user gets this result set instead.
FIRSTNAME LASTNAME STREET CITY
========= =========== ================= ============
Joe Smith 3820 N. 1536th St New Yolk
That's the basic idea. Now let me make it a bit more practical. Instead of searching a field for an exact match, let's look for the search string anywhere in the target field. While we're at it, let's expand the search to include street and city.
Here is the source code for the CL and RPG programs I used to test this technique. The first is ILE CL program QAD110C.
pgm (&Option &Search)
dcl &Option *char 1
dcl &Search *char 10
dcl &Status *char 1
call qad110r (&Status &Option &Search)
if (&Status *ne ' ') do
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) +
MSGDTA('Status was' *bcat &Status) +
MSGTYPE(*ESCAPE)
enddo
Here's RPG program QAD110R. Notice the cursor declaration (in blue).
H dftactgrp(*no) actgrp(*caller)
Fqsysprt o f 132 printer
D Qad110R pr
D Status 1a
D Select 1a
D Search 10a
D Qad110R pi
D Status 1a
D Select 1a
D Search 10a
D SearchString s 12a varying
D HostVariables ds
D FirstName 12a
D LastName 12a
D Street 20a
D City 16a
C/exec sql
C+ set option closqlcsr=*endmod
C/end-exec
C/exec sql
C+ declare Input cursor for
C+ select Firstname, LastName, Street, City
C+ from people
C+ where (case when :Select = 'F' then Upper(FirstName)
C+ when :Select = 'L' then Upper(LastName)
C+ when :Select = 'S' then Upper(Street)
C+ when :Select = 'C' then Upper(City)
C+ end) like Upper(:SearchString)
C/end-exec
C eval *inlr = *on
C eval Status = ' '
C eval SearchString = '%' + %trim(Search) + '%'
C/exec sql
C+ open Input
C/end-exec
C if SqlStt <> *zeros
C eval Status = 'O'
C return
C endif
C dou '0'
C/exec sql
C+ fetch Input into :HostVariables
C/end-exec
C if SqlStt = '02000'
C leave
C endif
C if SqlStt > '02000'
C eval Status = 'F'
C return
C endif
C except PLine
C enddo
C
C return
Oqsysprt e PLine 1
O FirstName
O LastName +0001
O Street +0001
O City +0001
Let me try to offer some explanation of how this works. Through some mechanism, the user indicates which field he wishes to search and enters a search string. This is translated into a call to QAD110C. The following example shows the call needed to look for "smith" in the city field.
CALL PGM(QAD110C) PARM(C SMITH)
QAD110C passes these two values to QAD110R, which calls them Select and Search. (I also included a status parameter in order to know whether or not the program ran successfully.) Let's look at QAD110R in more detail.
The Set Option command tells SQL that all cursors are to be closed when the module ends. This ensures that the cursor closes whether the program ends normally or abnormally.
Next is the cursor declaration, which defines how the data is to be accessed. The cursor declaration is similar in function to an F spec in an RPG program that uses native I/O operations. When the system opens the cursor named Input, the current value of Select determines which CASE option will be used for record selection. Notice the LIKE predicate, which matches character patterns. Take a look down a few lines, where the EVAL operation loads SearchString. The value of SearchString, once the EVAL has done its work, is the search string without leading and trailing blanks, surrounded by two percent signs, which serve as wild cards. For example, if the search string is smith, variable SearchString is %smith%. Surrounding the search string with wild cards tells the SQL engine that smith my be preceded or followed by other characters. Notice also the UPPER functions. Since the data is translated to uppercase before comparison, the search is not case sensitive.
After opening the cursor, the program reads the result set from beginning to end, printing each record as it reads. This is the way I determined which records were selected.
--Ted
RELATED STORIES
Alternate SQL Row-Selection Criteria, Take 2
Alternate SQL Row-Selection Criteria
|
