[Solved] How to retrieve results if second table of join is missing
I am joining 2 tables with about a million records in each table. I want only those records where there is not a matched value in the second joined table. Where 'is missing' did not work. With the example below, I want the EMPDATE.PIN files that do not have a match in TRAINING.
I can join the fields and create a hold file and on that hold file create the criteria which works but I do not want to retrieve all million records in a hold file to find the 500 or so that are not in the second table.
Does anyone have a suggestion on how to do this? Most likely that will not be the case with the example below but it shows the idea.
SET ASNAMES = ON JOIN EMPDATA.EMPDATA.PIN IN EMPDATA TO MULTIPLE TRAINING.TRAINING.PIN IN TRAINING AS J2 END TABLE FILE EMPDATA PRINT PIN AS 'EMPDATAPIN' PIN AS 'TRAININGPIN' COURSECODE EXPENSES ON TABLE HOLD AS TEMP01 END
TABLE FILE TEMP01 PRINT EMPDATAPIN COURSECODE EXPENSES WHERE TRAININGPIN IS MISSING; ON TABLE HOLD AS TEMP02 ENDThis message has been edited. Last edited by: Geri,
This will change the JOIN to act like an OUTER JOIN.
Then try running your request again with the WHERE clause in the first TABLE FILE statement.
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
August 17, 2011, 03:48 PM
ERINP
....and thats the difference between Virtuoso and Gold Member...lolThis message has been edited. Last edited by: ERINP,
WebFOCUS 7.6.9
Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5 Reporting Server OS/400 V5R4M0 Outputs: HTML, Excel, PDF, CSV, and Flat Files
August 17, 2011, 03:52 PM
j.gross
MATCH FILE, which merges complete results extracted from two datasources, is inadvisable, if either of the two extracts will be voluminous. (That depends on the selection criteria imposed in your real-world situation.)
You are essentially looking for an "if not exists" type of test. CONTAINS and OMITS test for existence of a suitable "child" instance (an instance in the Many side of a One-to-Many join). OMITS is the proper tool:
JOIN PIN IN EMPDATA TO MULTIPLE PIN IN TRAINING END
DEFINE FILE EMPDATA MATCHED/A1= IF TRAINING.TRAINING.PIN EQ EMPDATA.EMPDATA.PIN THEN 'Y' ELSE 'N'; END
TABLE FILE EMPDATA "EMPLOYEES WITH NO TRAINING RECORDS" "" PRINT EMPDATA.EMPDATA.LASTNAME NOPRINT BY TOTAL EMPDATA.EMPDATA.LASTNAME BY PIN IF MATCHED OMITS 'Y' ON TABLE SET ALL ON END
Here, OMITS is applied to a *defined* alpha field associated with the Training side of the join; and SET ALL=ON ensures that a row will be retrieved (with default values for the child columns), and MATCHED will be evaluated as 'N', when there is no matching child instance.
Note that the test assumes that blanks (or zero) is not a valid key value.This message has been edited. Last edited by: j.gross,
August 17, 2011, 04:13 PM
j.gross
quote:
SET ASNAMES = ON JOIN EMPDATA.EMPDATA.PIN IN EMPDATA TO MULTIPLE TRAINING.TRAINING.PIN IN TRAINING AS J2 END TABLE FILE EMPDATA PRINT PIN AS 'EMPDATAPIN' <--- 1 PIN AS 'TRAININGPIN' <--- 1 COURSECODE EXPENSES ON TABLE HOLD AS TEMP01 END
TABLE FILE TEMP01 PRINT EMPDATAPIN COURSECODE EXPENSES WHERE TRAININGPIN IS MISSING; <--- 2 ON TABLE HOLD AS TEMP02 END
Two comments to your code: 1. You need to qualify the fieldnames. As it stands, *both* columns will report the value in EMPDATA.EMPDATA.PIN. 2. If your key field is not nullable, IS MISSING will never be true (regardless of SET ALL).