Case-Insensitive Programming - A Technical Tip

submitted by Steve Kontos.

I recently had a request to select customers by city and state. In the example below, I had to join the credit master file to the customer master for the city and state information. No problem there. The problem was entering "BROOKLYN, NY" on the prompt screen did not select "Brooklyn, NY" because of the difference in case.

OPNQRYF

The solution below is contained in the very last parameter of the OPNQRYF statement -- SRTSEQ. I specified the system-supplied, shared-weight, translation table, QSYSTRNTBL.

That keyword SRTSEQ appears on many CL commands, including CRTBNDRPG, CRTDSPF and CHGJOB to name a few. Sorting sequence is also a system value that you can set to globally change how the system collates the letters a-z and A-Z, though I wouldn’t recommend you try CHGSYSVAL QSRTSEQ(QSYSTRNTBL).

0........1.........2.........3.........4.........5.........6.........7.........8
12345678901234567890123456789012345678901234567890123456789012345678901234567890

      OPNQRYF    FILE((CREDMST) (CUSTMST)) FORMAT(CREDMST)   +
                   QRYSLT('                                  +
                   RIDEL  *NE  "D"                           +
                   *AND     RICMP  *GE  ' || &FCOMP || '     +
                   *AND     RICMP  *LE  ' || &TCOMP || '     +
                   *AND     RICUST *GE "' || &FCUST || '"    +
                   *AND     RICUST *LE "' || &TCUST || '"    +
                   *AND     RIACNO *GE "' || &FACNO || '"    +
                   *AND     RIACNO *LE "' || &TACNO || '"    +
                   *AND     RMCMGR *GE "' || &FCRMGR || '"   +
                   *AND     RMCMGR *LE "' || &TCRMGR || '"   +
                   *AND     RMCITY *GE "' || &FCITY || '"    +
                   *AND     RMCITY *LE "' || &TCITY || '"    +
                   *AND     RMSTAT *GE "' || &FSTAT || '"    +
                   *AND     RMSTAT *LE "' || &TSTAT || '" ') +
                 KEYFLD((RICMP) (RIACNO) (RICUST))           +
                 JFLD((CREDMST/RICMP  CUSTMST/RMCMP  *EQ)    +
                      (CREDMST/RICUST CUSTMST/RMCUST *EQ))   +
                 JDFTVAL(*YES) JORDER(*FILE)                 +
                 SRTSEQ(QSYSTRNTBL)

This shared-weight table only translates the lowercase letters a-z. No other characters are affected.

Logical Files

Here is the DDS for a simple table with a code and description, keyed by company and code:

0........1.........2.........3.........4.........5.........6.........7.........8
12345678901234567890123456789012345678901234567890123456789012345678901234567890

     A                                      REF(ZZDTADCT)
     A          R SERVICE

     A            KSCONO    R               REFFLD(CONO)
     A            KSSERV        10          TEXT('Service code')
     A                                      COLHDG('Service' ' Code ')
     A            KSDESC        40          TEXT('Service code description')
     A                                      COLHDG('Service description')
     A            KSMDTE    R               REFFLD(MDTE)
     A            KSMTIM    R               REFFLD(MTME)
     A            KSMUSR    R               REFFLD(MUSR)

     A          K KSCONO
     A          K KSSERV

Here is the DDS for a logical file keyed by the description:

0........1.........2.........3.........4.........5.........6.........7.........8
12345678901234567890123456789012345678901234567890123456789012345678901234567890

     A                                      UNIQUE
     A                                      ALTSEQ(QSYSTRNTBL)
     A          R SERVICE                   PFILE(KWSERVICE)

     A          K KSCONO
     A          K KSDESC
     A          K KSSERV                    NOALTSEQ

In addition to making the description field the key to the file, I also wanted all upper and lower case descriptions to appear in a logical order, ignoring the case. For example, ‘UPS Ground 2nd Day’ should appear just before ‘UPS GROUND 3rd DAY’ or ‘ups ground 3rd day’.

The ALTSEQ keyword specifies the alternate collating sequence. The parameter is the shared-weight table QSYSTRNTBL.

The NOALTSEQ keyword indicates that those fields should be sequenced the good, old-fashioned way, by hexadecimal value, not using translation table QSYSTRNTBL.

Interactive SQL

In interactive SQL, you can also set your environment so that SELECT statements do not rely on case sensitive input. This allows you to type something like...

select * from custmast where rmname like '%smith%'

...and select names having SMITH, Smith or SmItH.

In your SQL session, press F13=Services and take option 1: "Change session attributes."

Press PageDown. For "Sort sequence", enter QSYSTRNTBL. Now, your select statements will be insensitive to case.

SRTSEQ(*LANGIDSHR) LANGID(ENU)

In addition to the above, you can also specify SRTSEQ(*LANGIDSHR) LANGID(ENU) which means "use the shared-weight table for the English language." This would be equivalent to using QSYSTRNTBL. Enjoy!

Steve Kontos