Joining the Same File Twice with OPNQRYF
From: Mark Brown To: All
I seem to come across the most unusual ways of processing files. I need to know if someone has ever joined two of the same file to a third file. (For example, I have a customer representative file and an employee master file. I want to join the employee master file to the rep file twice to get the rep's name and his supervisor's name.) The error I get tells me that the Last Name field is found in more than one record format. My OPNQRYF statement is shown in 7.
I seem to come across the most unusual ways of processing files. I need to know if someone has ever joined two of the same file to a third file. (For example, I have a customer representative file and an employee master file. I want to join the employee master file to the rep file twice to get the rep's name and his supervisor's name.) The error I get tells me that the Last Name field is found in more than one record format. My OPNQRYF statement is shown in Figure 7.
I forgot to mention that a second OPNQRYF statement (using different files, but trying to do the same thing with the employee master file) will be processed immediately after this one and just before the calling of the RPG program.
From: Ted Holt To: Mark Brown
You can join the same file more than once in the same query. In the example you gave, you need to define two last name fields and two first name fields-one for the rep and one for the supervisor-in the KRM105W2 file. I'm assuming you've already done that, and the supervisor fields are SPLNAM and SLFNAM, and the rep fields are KRLNAM and KRFNAM. If so, you only need to add two more mapped fields for the rep:
MAPFLD((KRLNAM '3/KRLNAM') + (KRFNAM '3/KRFNAM'))
As for the other OPNQRYF, if the OPNID parameter is the same as this one (EMPREFFP), you've got a problem. I've never tried this, but I believe two OPNQRYF definitions can coexist over the same file if you give one of them a different OPNID. From what you said, you're not using the same primary file in the other OPNQRYF, so you should be okay.
From: Mark Brown To: Ted Holt
Thanks for the tip. I figured this out about an hour after I posted the message. Am I correct in my assumption that if a primary file has any ambiguous field names in it, that all ambiguous fields need to be qualified whether they are used or not? I qualified all fields that appeared in more than one file and things seem to work fine for the first OPNQRYF, but the second one had fields in the primary file that appeared in a secondary join file. I was required to qualify all fields that appeared in both files even if they weren't used.
From: Ted Holt To: Mark Brown
I didn't think you had to qualify ambiguous field names if you weren't using them (i.e., they're not in the file named in the FORMAT parameter, or not used in the QRYSLT parameter). I'll have to double check that when I get a little time.
TechTalk: Joining the Same File Twice With OPNQRY
Figure 7 OPNQRYF to Join File Twice
OVRDBF FILE(KRM105W2) TOFILE(EMPREPPF) SHARE(*YES) OPNQRYF FILE((EMPREPPF) (LOSRTOPF) (KRMEMPPF) (KRMEMPPF)) + FORMAT(KRM105W2) QRYSLT(&QRYSLT2) KEYFLD((ERSUPV) (ERAGNT)) + JFLD((ERFND# LOFND#) (ERMEMB LOMEMB) (ERMSUB LOMSUB) + (ERAGNT 3/KREMP#) (ERSUPV 4/KREMP#)) JDFTVAL(*YES) + MAPFLD((SPLNAM '4/KRLNAM') (SPFNAM '4/KRFNAM')) SEQONLY(*NO)
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online