Re: Quick join-logical question

To help others, here is the answer (probably) that you found - I found it in the 7.3 version of Database Programming - the main idea is to use relative file numbers from the JOIN keyword and use RENAME. And JDFTVAL provides something like a left outer join. Relative file numbers seem similar to aliases we use in SQL JOINs, and like the T1, T2, etc., that we use in Query for i.

PF1
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R RCD1
     A            NBR            3
     A            NAME          10          DFT('none')
     A            MGRNBR         3
     A

JLF
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A                                      JDFTVAL
     A          R JOINREC                   JFILE(PF1 PF1)
     A          J                           JOIN(1 2)
     A                                      JFLD(MGRNBR NBR)
     A            NBR                       JREF(1)
     A            NAME                      JREF(1)
     A            MGRNAME                   RENAME(NAME)
     A                                      JREF(2)
     A

Cheers
Vern

On 8/27/2024 6:33 AM, Patrik Schindler wrote:

Hello James,

Am 27.08.2024 um 02:25 schrieb James H. H. Lampert via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>:

It's been a while since I've set up a DB2 join-logical.

Suppose in my master file, I have two fields that refer to an employee roster file: "Tech. Advisor" and "Tech. Supervisor."

I'm guessing there's a way to join the master file to the employee roster file twice, but I don't know that for sure, and neither do I recall exactly how.

Wasn't a similar question popping up just recently? Maybe even from me?
[…]
Yes, but there hasn't been a definitive answer.

I'll have time to RTFM tomorrow.

I think I found the answer in the classic "DB2 for AS/400 Database Programming", SC41-5701, chapter about logical files. One of the missing documentation gems I discovered just a couple of Months ago. This extensively describes how to handle PFs and LFs and their associated capabilities.

I take it that nothing substantial has changed since then — in the last two decades — because it's all about classic files and not about SQL.

Highly recommended reading as a refresh, maybe? ;-)

:wq! PoC