Normalized databases contain many files, many of them with multiple key fields. Rarely can you retrieve all the information you need for a job from just one of them. Whether you need data from 2 files or 20, OPN-QRYF's powerful joining capabilities let you give end users the information they need to get their jobs done.

In this article, you'll learn about some advanced OPNQRYF join techniques that can help you with everyday information retrieval from normalized databases. It is assumed that you already have a working knowledge of OPNQRYF. For some information on basic OPNQRYF join methods, see "Using OPNQRYF to Join Files," MC, July 1995.

Joining on Two or More Fields

Sometimes one field is enough to link two files. For instance, look at the order header and order detail files in 1. They share a common order number. The header record for order number 12345 contains information for the detail records of order number 12345. Similarly, the shipment header and shipment detail files have a common shipment number.

Sometimes one field is enough to link two files. For instance, look at the order header and order detail files in Figure 1. They share a common order number. The header record for order number 12345 contains information for the detail records of order number 12345. Similarly, the shipment header and shipment detail files have a common shipment number.

But matching shipments to orders is a different story. A shipment may include lines from multiple sales orders. That's why each record of the shipment detail file contains an order number and a line number.

Now, suppose I want to compare the actual quantity shipped to the quantity the customer ordered. I have to match shipment information to order information on two fields-order number and line number. 2 illustrates how this type of join can be done with the OPNQRYF command.

Now, suppose I want to compare the actual quantity shipped to the quantity the customer ordered. I have to match shipment information to order information on two fields-order number and line number. Figure 2 illustrates how this type of join can be done with the OPNQRYF command.

SHP001PF is a physical file with no members. It has all the fields in ORDD, plus SHIP#, SHIPLN, SHPQTY, and the calculated field, VARQTY. RPG program SHP001RG is coded to read SHP001PF, but the OVRDBF to the primary join file (ORDD) makes it read the data extracted by OPNQRYF instead.

The JFLD (Join field) parameter specifies that the ORDER# field in the first file (the order detail file, ORDD) must match the ORDER# field in the second file (the shipment detail file, SHPD) and that the ORDLN field in the first file must match the ORDLN field in the second file.

The MAPFLD (Mapped field) parameter tells OPNQRYF to retrieve the values of ORDER# and ORDLN from the first file (ORDD) and to calculate variance quantity (VARQTY) as the quantity shipped minus quantity ordered. This number will be positive for overshipments and negative for undershipments.

Since this is an inner join, we can code join criteria in the Query select (QRYSLT) parameter instead. It usually doesn't matter whether you join in QRYSLT or JFLD, but there are some nice joining techniques you can use with QRYSLT that won't work with JFLD, as you'll find out later. 3 contains the same OPNQRYF we just saw, except with the join criteria moved from JFLD to QRYSLT. Either will produce the same results.

Since this is an inner join, we can code join criteria in the Query select (QRYSLT) parameter instead. It usually doesn't matter whether you join in QRYSLT or JFLD, but there are some nice joining techniques you can use with QRYSLT that won't work with JFLD, as you'll find out later. Figure 3 contains the same OPNQRYF we just saw, except with the join criteria moved from JFLD to QRYSLT. Either will produce the same results.

You can join three or more files as easily as you can two. The query we just examined did not tell us what customer we were shipping to. To get that information, we need to include the CUST# field from the order header file, ORDH. In 4, I've added criteria to join ORDD to ORDH on their common order number. The figure contains two OPNQRYF commands. The first shows you how to code this join using the JFLD parameter; the second uses the QRYSLT parameter.

You can join three or more files as easily as you can two. The query we just examined did not tell us what customer we were shipping to. To get that information, we need to include the CUST# field from the order header file, ORDH. In Figure 4, I've added criteria to join ORDD to ORDH on their common order number. The figure contains two OPNQRYF commands. The first shows you how to code this join using the JFLD parameter; the second uses the QRYSLT parameter.

Joining on an Inequality

So far we've considered only values that match. Sometimes you need to combine information that doesn't match.

Suppose we need to know which shipments were late. The order detail file has the latest acceptable ship date in field SHPDTL. The shipment header file has the actual ship date. We need to restrict the join to cases where the latest allowable ship date is before (less than) the actual ship date.

Take a look at 5. The JFLD parameter in the OPNQRYF command says that the ORDD and SHPD files must match on order number and order line number, the SHPD and SHPH files must share a common shipment number, and the latest allowable shipment date must be before the actual ship date. (This could also be done with just the QRYSLT parameter.)

Take a look at Figure 5. The JFLD parameter in the OPNQRYF command says that the ORDD and SHPD files must match on order number and order line number, the SHPD and SHPH files must share a common shipment number, and the latest allowable shipment date must be before the actual ship date. (This could also be done with just the QRYSLT parameter.)

Some customers get upset if our shipment arrives too early, since early shipments don't fit just-in-time (JIT) manufacturing. To please them, we also have an earliest allowable ship date (SHPDTE) in the order detail file. Suppose we want to know which shipments were outside of the shipment "window" (the range of earliest and latest allowable ship dates). We restrict the join to cases in which the actual ship date is before the earliest allowable ship date or after the latest allowable ship date. Since all join field pairs in the JFLD parameter use the AND operator, we can't use the JFLD parameter to join the date fields. We can use the OR operator in QRYSLT, though. The code to find shipments outside the window is in 6. The first OPNQRYF uses both the JFLD and QRYSLT parameters. The second does all joining using the QRYSLT parameter.

Some customers get upset if our shipment arrives too early, since early shipments don't fit just-in-time (JIT) manufacturing. To please them, we also have an earliest allowable ship date (SHPDTE) in the order detail file. Suppose we want to know which shipments were outside of the shipment "window" (the range of earliest and latest allowable ship dates). We restrict the join to cases in which the actual ship date is before the earliest allowable ship date or after the latest allowable ship date. Since all join field pairs in the JFLD parameter use the AND operator, we can't use the JFLD parameter to join the date fields. We can use the OR operator in QRYSLT, though. The code to find shipments outside the window is in Figure 6. The first OPNQRYF uses both the JFLD and QRYSLT parameters. The second does all joining using the QRYSLT parameter.

Joining on Calculated Fields

Let's say our agreement with customers is that we may undership or overship an order by 3 percent. For instance, if a customer orders 100 widgets, we may ship as few as 97 or as many as 103, and the order will be considered filled. How would we find orders that were not shipped complete? How would we find overshipments?

We could use code like that in 7. The order detail file and shipment detail file must still match on order number and order line number, but the quantity shipped may or may not match the quantity ordered. The order number and order line number may be matched in the JFLD or QRYSLT parameters, but the quantities must be compared in QRYSLT. 7 shows this join using both JFLD and QRYSLT. We could also use just the QRYSLT parameter.

We could use code like that in Figure 7. The order detail file and shipment detail file must still match on order number and order line number, but the quantity shipped may or may not match the quantity ordered. The order number and order line number may be matched in the JFLD or QRYSLT parameters, but the quantities must be compared in QRYSLT. Figure 7 shows this join using both JFLD and QRYSLT. We could also use just the QRYSLT parameter.

Joining on Fields of Different Types

If your database is well designed, you will probably not have to join data of different types. Life is not perfect, however. I once worked with a manufacturing system where seven-digit inventory item numbers were stored in various files as seven-digit packed numbers, seven-digit zoned numbers, ten-digit zoned numbers (the first three digits were always zeros), or fifteen-byte character values (the last eight bytes were always blank). Maybe you're in that kind of situation.

To join data of different types, you have to convert all data to one common type.

Let's imagine three files with item numbers like those I just described. FILEA contains fifteen-byte character item numbers in field ITEMA. FILEB contains seven-digit packed item numbers in field ITEMB. FILEC contains ten-digit zoned item numbers in field ITEMC. Let's join the three files on item number.

I prefer to use character as the common data type unless all the join fields are numeric. Since item numbers have only seven digits, I'll convert all item numbers to seven-byte character values, as shown in 8.

I prefer to use character as the common data type unless all the join fields are numeric. Since item numbers have only seven digits, I'll convert all item numbers to seven-byte character values, as shown in Figure 8.

Since ITEMA is a character variable, I can use the substring (%SST) function to extract the leftmost seven bytes. ITEMB and ITEMC are numeric, so I've used the %DIGITS function to convert them to character values. %DIGITS converts the digits 0 through 9 to characters, and it ignores signs, decimal points, and currency symbols. ITEMB is seven digits long, so the %DIGITS function is all we need to use to convert it to characters. ITEMC is 10 digits long. We need to convert only the last seven digits. Here, I've used a combination of %SST and %DIGITS to do the conversion.

If You Can't Beat 'em, Join 'em!

When it comes to joining files temporarily, you just can't beat OPNQRYF. Put these powerful techniques to work for you.

Ted Holt is an associate technical editor for Midrange Computing and the author of Open Query File Magic, available by calling 800-447-5665.

Advanced Join Techniques Using OPNQRYF

Figure 1: Order and Shipment Files

 Order Header File (ORDH) ORDER# (key) Sales order number CUST# Customer number PO# Purchase order number REP# Sales rep number Order Detail File (ORDD) ORDER# (key) Sales order number ORDLN (key) Sales order line number ITEM# Item number ORDQTY Quantity ordered SHPDTE Earliest allowable ship date SHPDTL Latest allowable ship date PRICE Unit price Shipment Header File (SHPH) SHIP# (key) Shipment number CARR Carrier code PRO# Shipment "pro" number SHPDT Actual ship date Shipment Detail File (SHPD) SHIP# (key) Shipment number SHIPLN (key) Shipment line number ORDER# Sales order number ORDLN Sales order line number SHPQTY Quantity shipped 
 
Advanced Join Techniques Using OPNQRYF
 
Figure 2: Joining Shipment Details to Order Details
 
 PGM OVRDBF FILE(SHP001PF) + TOFILE(ORDD) + SHARE(*YES) OPNQRYF FILE((ORDD) (SHPD)) + FORMAT(SHP001PF) + KEYFLD((ORDER#) (ORDLN)) + JFLD((1/ORDER# 2/ORDER#) + (1/ORDLN 2/ORDLN)) + MAPFLD((ORDER# '1/ORDER#') + (ORDLN '1/ORDLN') + (VARQTY 'SHPQTY - ORDQTY')) CALL PGM(SHP001RG) CLOF OPNID(ORDD) DLTOVR FILE(SHP001PF) ENDPGM 
 
Advanced Join Techniques Using OPNQRYF
 
Figure 3: Joining with QRYSLT
 
 OPNQRYF FILE((ORDD) (SHPD)) + FORMAT(SHP001PF) + KEYFLD((ORDER#) (ORDLN)) + QRYSLT('1/ORDER# *EQ 2/ORDER# & + 1/ORDLN *EQ 2/ORDLN') + MAPFLD((ORDER# '1/ORDER#') + (ORDLN '1/ORDLN') + (VARQTY 'SHPQTY - ORDQTY')) 
 
Advanced Join Techniques Using OPNQRYF
 
Figure 4: Joining Three Files
 
 JFLD Method OPNQRYF FILE((ORDD) (SHPD) (ORDH)) + FORMAT(SHP002PF) + KEYFLD((CUST#) (ORDER#) (ORDLN)) + JFLD((1/ORDER# 2/ORDER#) + (1/ORDLN 2/ORDLN ) + (1/ORDER# 3/ORDER#)) + MAPFLD((ORDER# '1/ORDER#') + (ORDLN '1/ORDLN') + (VARQTY 'SHPQTY - ORDQTY')) QRYSLT Method OPNQRYF FILE((ORDD) (SHPD) (ORDH)) + FORMAT(SHP002PF) + KEYFLD((CUST#) (ORDER#) (ORDLN)) + QRYSLT('1/ORDER# *EQ 2/ORDER# & + 1/ORDLN *EQ 2/ORDLN & + 1/ORDER# *EQ 3/ORDER#') + MAPFLD((ORDER# '1/ORDER#') + (ORDLN '1/ORDLN') + (VARQTY 'SHPQTY - ORDQTY')) 
 
Advanced Join Techniques Using OPNQRYF
 
Figure 5: Joining on an Inequality
 
 OPNQRYF FILE((ORDD) (SHPD) (SHPH)) + FORMAT(SHP003PF) + KEYFLD((ORDER#) (ORDLN)) + JFLD((1/ORDER# 2/ORDER#) + (1/ORDLN 2/ORDLN ) + (2/SHIP# 3/SHIP# ) + (1/SHPDTL 3/SHPDT *LT)) + MAPFLD((ORDER# '1/ORDER#') + (ORDLN '1/ORDLN')) 
 
Advanced Join Techniques Using OPNQRYF
 
Figure 6: Joining Using the OR Operator
 
 JFLD and QRYSLT Method OPNQRYF FILE((ORDD) (SHPD) (SHPH)) + FORMAT(SHP003PF) + KEYFLD((ORDER#) (ORDLN)) + QRYSLT('1/SHPDTL *LT 3/SHPDT *OR + 1/SHPDTE *GT 3/SHPDT') + JFLD((1/ORDER# 2/ORDER#) + (1/ORDLN 2/ORDLN) + (2/SHIP# 3/SHIP#)) + MAPFLD((ORDER# '1/ORDER#') + (ORDLN '1/ORDLN')) QRYSLT Method OPNQRYF FILE((ORDD) (SHPD) (SHPH)) + FORMAT(SHP003PF) + KEYFLD((ORDER#) (ORDLN)) + QRYSLT('1/ORDER# *EQ 2/ORDER# *AND + 1/ORDLN *EQ 2/ORDLN *AND + 2/SHIP# *EQ 3/SHIP# *AND + (1/SHPDTL *LT 3/SHPDT *OR + 1/SHPDTE *GT 3/SHPDT)') + MAPFLD((ORDER# '1/ORDER#') + (ORDLN '1/ORDLN')) 
 
Advanced Join Techniques Using OPNQRYF
 
Figure 7: Joining on an Expression
 
 OPNQRYF FILE((ORDD) (SHPD)) + FORMAT(SHP004PF) + KEYFLD((ORDER#) (ORDLN)) + QRYSLT('SHPQTY *LT (ORDQTY * 0.97) + *OR SHPQTY *GT (ORDQTY * 1.03)') + JFLD((1/ORDER# 2/ORDER#) + (1/ORDLN 2/ORDLN)) + MAPFLD((ORDER# '1/ORDER#') + (ORDLN '1/ORDLN')) 
 
Advanced Join Techniques Using OPNQRYF
 
Figure 8: Joining Data of Different Types
 
 OPNQRYF FILE((FILEA) (FILEB) (FILEC)) + FORMAT(SOMEFILE) + MAPFLD((WORKA '%SST(ITEMA 1 7)') + (WORKB '%DIGITS(ITEMB)') + (WORKC '%SST(%DIGITS(ITEMC) 4 7)')) + JFLD((WORKA WORKB) (WORKA WORKC)) 

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

Also Read

9 data governance strategies that will unlock the potential of your business data

Data observability: The missing piece in your data integration puzzle

From Legacy to Modern: Upgrading Your BI Strategy with LANSA BI for IBM i