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,
Do you have a filter on Table3?
Have you tries SET ALL=PASS ?
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
SUM FIELD2 BY MYVAR1
AFTER MATCH HOLD OLD-OR-NEW (that's the union)
..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?
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)
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.
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.
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.
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.
I tried using SET ALL=PASS and it didnt return what I was looking for. MATCH Processing works great for me.
|Powered by Social Strata|