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  iWay Software Product Forum on Focal Point    Left Outer Join and Filters

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Left Outer Join and Filters
 Login/Join
 
Member
posted
I thought I'd have a go at being the first iWay customer to post a query.

We're using iWay 5.1.2 on NT with an ODBC connection to DB2/400, extracting data to FOCUS files for use in WebFOCUS. We also run ETLs against the created FOCUS files to create more complex views or summary views of detail data.

The ETL issue is that I'm joining two FOCUS files using a "Left Outer Join" (all left source columns with right source columns that match on the join field) but the output file is only to be the records that don't have a match in the right source. To achieve this I'm adding in a Filter box with the condition that a right source field IS NULL.

(that is, if there are 5000 records in the left source and 4000 matching records from the right source then the output required is the 1000 records from the left source that don't match)

The trouble is that no data is returned at all (it seems that the filter is applied to the right source table before the join is performed, despite the filter dialog box following the join in the ETL).

I can work around this by performing the join in an ETL and then filtering out the matching records in a subsequent ETL. However, this seems messy and my gut tells me I'm missing something.

Any advice would be appreciated. Confused
 
Posts: 27 | Location: Sydney, Australia | Registered: May 27, 2003Report This Post
Member
posted Hide Post
Thanks for posting, Andrew.

You have stumbled on a known issue that affects some cross-database outer joins with WHERE clauses. Technically, the issue is in the adapter, not in ETL Manager, so other queries of this type will be affected regardless of the API used to perform the join. Since it has not critically affected any customers to date, we currently expect to fix it in a future release.

There are two primary ways to mitigate the problem:

1. Perform the join in a single database; in other words, move the data to a single DBMS and perform the query there.

2. Perform the action in two ETL jobs (if you are using ETL Manager). The first job performs the outer join, and the next one applies the filter criterion.

Those are obviously workarounds to your problem, and we hope to get the issue resolved quickly so that they become unnecessary.

If you have other questions, please let us know what they are and we'll respond as quickly as possible.

Regards,
Jake Freivald
Director
iWay Software
 
Posts: 20 | Location: NY, NY | Registered: June 19, 2003Report This Post
Platinum Member
posted Hide Post
Andrew,

There may be a couple of other approaches. Since these are FOCUS files, I'm assuming that you can do FOCUS based Joins and TABLE FILE.

1) Use MATCH logic
MATCH FILE allows you to do Venn Diagram type logic against 2 files. In this case you would want to do an OLD-NOT-NEW. For full details, check the documentation.

2) Use SET ALL=PASS
This would look something like this:
SET ALL=PASS
JOIN KEY1 AND KEY2... IN LEFT TO KEY1 AND KEY2... IN RIGHT AS J0
END
TABLE FILE LEFT
PRINT right fields
WHERE left-field EQ 'some-nonsense-value'
END

This technique is also discussed in the user manuals.

---------
Incidentally, if you're using SQL, the left outer join with a null condition on a right field only works if the right field is non-nullable. If the field on the right is nullable, then you could get false positives. A safer SQL based apporach is to use a correlated subquery:

SELECT *.right
FROM right
WHERE NOT EXISTS
(SELECT 1
FROM left
WHERE right.KEY1 = left.KEY1
AND right.KEY2 = left.KEY2
etc.)
 
Posts: 164 | Registered: March 26, 2003Report This Post
Expert
posted Hide Post
we've got db2/400 files and similar situations.
I went to a FUSE meeting the other day and attended a presentation by Rene Teatro on DB2 efficiencies; You might want to contact her and get her prez. It was very informative. One of the very interesting things she said is that in a lefthanded join,
if the fields you're referencing in your fex are all in the right (guest) file, sql doesn't even bother doing the join. Well, that makes sense, but i don't think i would have thought of it.
We have a set of standard joins that are performed at the beginning of EVERY fex.
hmmm.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Left Outer Join and Filters

Copyright © 1996-2020 Information Builders