Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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,
 
Report This Post
Virtuoso
posted Hide Post
Do you have a filter on Table3?
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
<WFUser>
posted
Have you tries SET ALL=PASS ?
 
Report 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, 2003Report 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.
 
Report 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, 2004Report 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.
 
Report 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, 2003Report This Post
Guru
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: 8206.10
Dev: 8201M
Prod:8009
-********************
 
Posts: 289 | Location: Houston,TX | Registered: June 11, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders