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
END

This message has been edited. Last edited by: Geri,

Geri,
Take a look at MATCH using OLD-NOT-NEW this will return records from the first request that do not have records in the second file.

ERINP

I have used match before but only on hold files. I will try match on the masters and let you know how well it works for this.

Thank you
Geri

SET ALL = ON

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.

....and thats the difference between Virtuoso
and Gold Member...lol

This message has been edited. Last edited by: ERINP,

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,

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).

Match works for what we need. Thanks!

Hi Geri,
Did you try
Where pin NE ''
Or Where table1.pin NE table2.pin
Sometimes this works.