OPNQRYF Has No "If" But You Can Fake It

Published: November 5, 2008

Hey, Ted:

We recently started adding Web customers to our customer master file, in which a customer ID is a field of seven characters. The ID's of Web customers consist of the letter "W" followed by six digits. Is there any way using OPNQRYF to change only the customer IDs that start with "W" to the customer number "5000000" for reporting purposes? Doing so would allow us to keep a lot of existing programs running without a lot of modification.

--John

OPNQRYF does not have conditional logic, John, but in your case, you can fake it out. Here's the type of OPNQRYF command you need.

OPNQRYF  FILE((SOMEFILE)) +                                    
   KEYFLD((CUSTNBR)) +                                          
   MAPFLD((WORK10  '%max((1/CustNbr *cat "2") "Z0000000")') +   
          (WORK20  '%sst(Work10 8 1) *cat %sst(Work10 1 7)') +  
          (WORK30  '%max(Work20 "15000000")') +                 
          (CUSTNBR '%sst(Work30 2 7)'))                         

Now let me explain how it works.

The first mapped field, WORK10, is the larger of two values--the customer number with a "2" appended to it, or the literal "Z00000000". I chose this literal because the value of the first seven digits lies between the highest possible Web customer number--W9999999--and the lowest possible non-Web customer number--0000001. For Web customers, WORK10 is Z0000000. For non-Web customers, WORK10 is the customer number followed by a two.

WORK20 reformats WORK10, moving the last digit to the front of the literal. Non-Web customers have their own ID's with a prefix of "2". Web customers have the value "0Z000000".

WORK30 takes the value 15000000 for Web customers and keeps the value of WORK20 for non-Web customers. The last seven digits of WORK30 are either the original customer number or 5000000.

The only thing left to do is to extract the last seven digits into CUSTNBR.

The following table summarizes the processing:

OPNQRYF FILE((WalshCust)) +
   KEYFLD((CUSTNBR)) +
   MAPFLD(+
    (WORK10 '%min((1/CustNbr  *cat 1/CustNbr *cat "2") +
                  ("W      "  *cat 1/CustNbr *cat "0"))') +
    (WORK20 '%max((%sst(Work10 8 7) *cat 1/CustNbr *cat %sst(Work10 15 1) +
                                *cat "2") +
                ("X      "          *cat 1/CustNbr *cat %sst(Work10 15 1) +
                                    *cat "0"))') +
      (WORK30 '%sst(Work20 15 2) *cat %sst(Work20 8 7)') +
      (WORK40 '%max(Work30 "015000000")') +
      (CUSTNBR '%sst(Work40 3 7)'))