[CLOSED] How to obtain missing records from a join?

I've tried combing through my manuals and I still can't figure this out.

I'm trying to join 3 different tables to extract data. I've joined table1 to table2 and output to a hold file. I've then joined the hold file to table3 and put to another hold file. The issue I'm having is that when I join the hold table and table3, I'm missing records because there is not any rows of data to return from table3. I would like focus to not discard the parent records from the hold file, instead just return the data with a nodata value. I've tried set all = on prior to joining the hold table to table3 but I'm still short records. Any ideas?

Thanks in advance.

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

dhagen

Virtuoso

posted

Hide Post

Do you have a filter on Table3?

Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004

Ignored post by dhagen posted

Show Post

posted

Have you tries SET ALL=PASS ?

susannah

Expert

posted

Hide Post

shart, you could always fall back on the MATCH command. It gives you absolute control, even tho it isn't as elegant.
MATCH FILE ONE
SUM FIELD1 etc BY MYVAR1
RUN
FILE TWO
SUM FIELD2 BY MYVAR1
AFTER MATCH HOLD OLD-OR-NEW (that's the union)
END
..there are 6 states for the match.
HOLD OLD (just the myvar1s that exist in file one
HOLD NEW (just the myvar1s that exist in file two)
HOLD OLD-AND-NEW (the intersection, gotta be in both)
HOLD OLD-OR-NEW (the union, just gotta be in either)
HOLD OLD-NOT-NEW (the complement of file2)
HOLD NEW-NOT-OLD (the complement of file1)
(i think i've got them all); Its the sledge-hammer way to do it, but you get perfect control.
You can take the result of the first match and match to the third file, and when you get good at it, you can match all 3 files with one match command. Does this help? or did you know this already and wanted to be more elegant?

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

posted

Thanks for everyone's help. I have tried the Set All = Pass. However, I don't have a filter on table 3. I can try that as well as the match function. I've never used the match function but I'll try anything once. haha

Here's the error message I'm getting.
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2599) NON-SQL SEGMENT IN HIERARCHY (OTHER INTERFACE PRESENT)
SELECT T2."ITPKG_EFF_DTE",T2."ITPKG_EXP_DTE",
T2."ITPKG_SEQSNP_QTY",T2."ITPKG_PKG_TYP",T2."ITPKG_PKG_NBR",
T2."ITPKG_SEQULD_QTY" FROM "NMM"."ITEM_PKG" T2 WHERE
(T2."ITPKG_ITEM_NBR" = ?) AND (T2."ITPKG_FACL_ID" = ?) AND
(T2."ITPKG_SUPL_NBR" = ?) FOR FETCH ONLY;
NUMBER OF RECORDS IN TABLE= 6495 LINES= 6495

The issue I'm having is that the join is trying to capture packaging data. But you may have multiple packaging records for one part number/supplier. I want to return the ones that have a expiration date of '12/31/9999' or null. However, I can't figure out how to do null.

jmdoyl1

Silver Member

posted

Hide Post

Have you tried IS MISSING (IS NOT MISSING) in your FOCUS code. That should translate to IS NULL (IS NOT NULL) at least for DB2.

Posts: 39 | Registered: January 26, 2004

Ignored post by jmdoyl1 posted

Show Post

posted

I've tried the IS MISSING but unfortunately it does not return any records at all. I'm thinking it may have to do with how I'm doing my joins. I think I'll try to MATCH to see if that works. Thanks for everyone's help.

susannah

Expert

posted

Hide Post

you're doing a "one-to-many join", thats what that is called. Without knowing what a 'null' looks like in your database, i can't tell you how to check for it, but here's an idea: you could check for DATE FROM 19990101 TO 20041212
or some such range so that whatever that 'null' actually contains, it wont pass your screening test.

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

vaayu

Guru

posted

Hide Post