Q: Is there a way to do conditional mapping with Open Query File (OPNQRYF)? For example, suppose you have an INVOICE file and a CUSTOMER file that both contain a customer name field. If the customer name field in the INVOICE file contains a value, can you instruct OPNQRYF to use it and otherwise have OPNQRYF use the customer name field in the CUSTOMER file?
- David Andrade
A: Conditional mapping with OPNQRYF is possible. However, there are some limitations. The way to accomplish it is through the use of the %MAX function on the MAPFLD parameter. For example, consider the following OPNQRYF statement:
OVRDBF FILE(CUSTOMER) SHARE(*YES) OVRDBF FILE(INVOICE) SHARE(*YES) OPNQRYF FILE((INVOICE) + (CUSTOMER)) FORMAT(INVOICE) + KEYFLD((INCUST)) + JFLD((INCUST CSCUST *EQ)) + MAPFLD((INNAME + '%MAX(INVOICE/INNAME CSNAME)'))
This statement joins an INVOICE file with a CUSTOMER file using the format of the INVOICE file. The two files are joined by customer number. The MAPFLD parameter selects the greater of either the customer name in the INVOICE file or the customer name in the CUSTOMER file. (To avoid the OPNQRYF error of referencing a field to itself, you must qualify INNAME as has been done in this example.) In most cases, this logic should retrieve the values you want because a customer name containing a value will always be greater than a blank customer name. The only problem is when there's a customer name in both files and the name in the CUSTOMER file is greater. In this case, the customer name in the CUSTOMER file will override the name in the INVOICE file. Since you said you wanted the customer name in the INVOICE file to be used if it exists, this solution may not be acceptable in your situation. However, if you simply want to select the greater of the two names, you should consider using this method.
- Ted Holt