Alternate SQL Row-Selection Criteria, Take 3

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