The problem: How do I join two tables in OpenQM?
Solution: L-Type Records
Let’s say we have a couple of tables files, SKUS and VENDORS
LIST DICT SKUS Page 1
@ID......... TYPE LOC........... CONV.. NAME........ FORMAT S/M ASSOC...
@ID D 0 SKUS 10L S
DESCR D 1 Description 24L S
VENDORS D 2 8R M
VDR L VENDORS VENDOR
S
4 record(s) listed
LIST DICT VENDORS Page 1
@ID......... TYPE LOC........... CONV.. NAME........ FORMAT S/M ASSOC...
@ID D 0 VENDORS 10L S
COMPANY D 1 32L S
ADDR D 2 Address 24L M
3 record(s) listed
According to the OpenQM documentation, the four fields of an L-Type record are:
1: L { descriptive text }
2: Id expression
3: File name
4 { File expression }
The key here, pun intended, is that we need to link an expression in one file to another. We aren’t limited to simple keys here… we can actually build complex expressions. The example in the documentation and given in the demo files extracts a key defined in the file as 3-7 while it links to the @ID of the target file, which is needs just the left half of the expression. Now, in my own example here, I’m using just a simple key to join the two files so that the VENDORS field in SKUS and the id field of the VENDORS file will match. When we LIST the SKUS file, we can list the vendor information as follows:
LIST SKUS DESCR VDR%COMPANY
SKUS...... Description............. Company.........................
5192 STAPLER, RED BOBCO LTD
VILE INDUSTRIES
2121 STAPLER, BLUE VILE INDUSTRIES
2 record(s) listed
And just like that, it works.
Now, I’m missing a piece of the puzzle here, because if you list both the company name and address in the skus listing, it has no association between the two values and ends up looking like this garbage:
list skus DESCR VDR%COMPANY VDR%ADDR
SKUS...... Description............. Company......................... Address.................
5192 STAPLER, RED BOBCO LTD JOBO MCDUGNUTS
VILE INDUSTRIES ACCOUNT EXECUTIVE
BOBCO LTD
1010 THREE TWO ONE ST
NOWHERESVILLE EX 00000
SNAKE SLITHERY
PRESIDENT
VILE INDUSTRIES
814 W CATAHULA AVE
TOXIC DUMP EX 00100
2121 STAPLER, BLUE VILE INDUSTRIES SNAKE SLITHERY
PRESIDENT
VILE INDUSTRIES
814 W CATAHULA AVE
TOXIC DUMP EX 00100
2 record(s) listed
Hrm…