Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to obtain missing records from a join?
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] How to obtain missing records from a join?
 Login/Join
 
<shart00>
posted
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,
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Do you have a filter on Table3?
 
Posts: 1100 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
<WFUser>
posted
Have you tries SET ALL=PASS ?
 
Reply With QuoteReport This Post
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? Wink
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
<shart00>
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.
 
Reply With QuoteReport This Post
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, 2004Reply With QuoteReport This Post
<shart00>
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.
 
Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
quote:
HOLD OLD

I tried using SET ALL=PASS and it didnt return what I was looking for. MATCH Processing works great for me.


-********************
Sandbox: 8205
Dev: 8201M
Prod:8009
-********************
 
Posts: 216 | Location: Houston,TX | Registered: June 11, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to obtain missing records from a join?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.