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.

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

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.)

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.