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

Ted Holt is IT manager of Manufacturing Systems Development for Day-Brite Capri Omega, a manufacturer of lighting fixtures in Tupelo, Mississippi. He has worked in the information processing industry since 1981 and is the author or co-author of seven books. 

---

MC Press books written by Ted Holt available now on the MC Press Bookstore.

		Complete CL: Fifth Edition Become a CL guru and fully leverage the abilities of your system. List Price $79.95 Now On Sale
		Complete CL: Sixth Edition Now fully updated! Get the master guide to Control Language programming. List Price $79.95 Now On Sale
		IBM i5/iSeries Primer Check out the ultimate resource and “must-have” guide for every professional working with the i5/iSeries. List Price $99.95 Now On Sale
		Qshell for iSeries Check out this Unix-style shell and utilities command interface for OS/400. List Price $79.95 Now On Sale