Mapping 2 different tables in one with ODI 10

0
votes

I am creating a "temporary" (transition) table : "Abis" which must be the copy (identical structure) of a table "A" with the addition of data and the update of several fields via other tables more recent (B, C, D, E).

I have a primary key based on 2 fields in "A" (A.a and A.b) that is present in "Abis" (Abis.a and Abis.b) as well as in "B" (B.a and B.b).

I made a full join between A and B: A.a = B.a and A.b = B.b.

What mapping I have to put to feed my "Abis" table on Abis.a and Abis.b, recovering all key combinations of A (A.a + A.b) as well as all key combinations of B (B.a + B.b) that aren't present in A.

I tested with "Case When A.a Not In B.a Than A.a Else B.a End"

But the query turns indefinitely.

To sum up: Target Datastore: Abis Diagram: A, B, C, D, E Join: A.a = B.a and A.b = B.b (Full join) Number of row: Table A ~ 6000, Table B ~ 40000 Software: ODI 10.1.3.5 (Oracle Data Integrator)

Thanks :)

1
oracleoracle10gmappingoracle-data-integrator
is it a question specific to Oracle Data Integrator ? if yes, add the oracle-data-integrator tag . If it's a purely sql based question, provide some sample data and expected o/p. - Kaushik Nayak

1 Answers

0
votes

Ok I almost solved my problem with the DECODE function

I tried the NVL function but it did not give exactly what I wanted.

The similar function to NVL is DECODE. https://www.techonthenet.com/oracle/functions/decode.php

  • Mapping on Abis.a > Decode (A.a, 0, B.a, A.a)
  • Mapping on Abis.b > Decode (A.b, 0, B.b, A.b)