OPNQRYF Has No “If” But You Can Fake It
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:
Your program will use the value of the CUSTNBR mapped field, not the CUSTNBR from the database file. It’s not elegant, but maybe it will save you some work. Please let me know how it goes. –Ted Congratulations, it worked. However, it also changed customers that start with other letters to 5000000. You assumed that all non-Web customers had customer ID’s of seven digits. That is not the case. We begin customer ID’s with other letters as well. I only want to change the customer numbers that start with “W” to 5000000. –John Sorry, John. That’s not the first time I assumed something and goofed up as a result. I have good news, though. You can still convert only the “W” customers to 5000000.
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)'))
The first work field, WORK10, appends a “2” to records with customer ID’s below W and a zero to the records of W customers and above.
WORK20 appends a “2” to records for customers whose IDs start with the letter X or above.
WORK30 moves the two suffixes generated by the previous steps to the front and moves the customer ID to the end.
WORK40 contains the customer number with its two prefix digits, or the special value 015000000, whichever is greater. This is the step that replaces “W” customers with the special value 5000000.
The last work field, CUSTNBR, extracts the customer number.
–Ted Thanks for all your efforts, Ted. It works great! –John
|
