Focal Point
[CLOSED] Datamigrator turns inner join into left outer

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/9287044986

February 28, 2018, 09:28 AM
Ronibi
[CLOSED] Datamigrator turns inner join into left outer
Hai

we have source table A that has to be inner joined with source table B. To be able to join them we have to transform an attribute in table A. Table A has an attribute called IBAN. We have to get the last ten positions of this field of 34 bytes. Because of this transformation of an attribute used in het join, Datamigrator 81M makes a hold file. And the inner join of two tables becomes a left outer join of the hold file of table A with table B.
We now solve this problem by excluding the rows with NULL values in table B.

But why transforming the inner join to an outer join???

Is there a solution that the inner is not changed to an outer?

Thnx
Ron

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS AppStudio 8.2.02M
WebFocus Datamanagement Console 8.1M
DMC
February 28, 2018, 10:28 AM
Clif
I've never seen the behavior you describe where an INNER JOIN becomes an OUTER. Have you tried just changing it back in the Join Editor? If that doesn't work I would suggest opening an InfoResponse case and provide the source synonyms and data flow.

That said, assuming your source is relational database tables, instead of doing a source transformation to extract the partial field value, do it in the Join Calculator. Replace the transformed field with a calculation on the original field:
SUBSTR(IBAN FROM 24 FOR 10)

March 01, 2018, 06:47 AM
Ronibi
Thnx for the advice Clif. The input is a table of 2 million records. Putting it in the join calculator means that during join DB2 has to do some work (conversion). It won't like that I presume resulting in a less performance. But we will give it a try one of these days.


WebFOCUS AppStudio 8.2.02M
WebFocus Datamanagement Console 8.1M
DMC
March 01, 2018, 06:53 AM
Ronibi
Addition: the JOIN editor says INNER JOIN. That isn't changed. But we see in our output that the job behaves like an outer join.


WebFOCUS AppStudio 8.2.02M
WebFocus Datamanagement Console 8.1M
DMC
March 01, 2018, 11:36 AM
FP Mod Chuck
Ronibi

I suggest you open a case with techsupport on this issue. It should not behave like an outer join.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats