2

Actually, there are total 4 tables invoked in this mapping: Market,Cost, A, B,

Read_sourceTB_B-----FIL1------->---------JNR4 \
    |                                     |     |
    |    Read_sourceTB_Market--\          |     |
    |    Read_sourceTB_Cost------JNR1--\  |     |
    |    Read_sourceTB_A-----------------JNR2   JNR5--->EXP... -->TGT
    |                       |             |     |
    |                       |             |     |
    |                       |             |     |
     ---------------------FIL2->---------JNR3 /


SQ_TABLEB --FIL1-> -- JNR1 \
        |               |   |
        |   SQ_TABLEA --|    JNR3-->EXP.... -->TGT  
        |               |   | 
        |--FIL2-> -- JNR2  /

**First **joinning condition

A LEFT JOIN B 
ON A.MEMBERSHIPID = B.MEMBERSHIPID
Where B.System_Code='University'

IF <First joinning condition> failed, then execute

**Second **joinning condition

A LEFT JOIN B ON 
A.address = B.address and A.phonenumber = B.phonenumber

Where B.System_Code='Policy'

Which transformation should I use? I don't know how to use Informatica, my version is Informatica Developer 10.5, please help me.Thanks!

I only know how to


A left join B on `condition`  `System_Code='University'`

left join B on `condition`   `System_Code='Policy'`

but I don't know how to make a decision for if A join B System_Code='University'failed,

then A join B System_Code='Policy'

John
  • 29
  • 4

1 Answers1

1

You need to join A with B (twice) based on two different condition and then join them back to one single pipeline for a decision/if-else condition. Also please note, all your left joins are actually inner join because you are using B.xxx='something' condition in the where clause.

So, considering above problem -

  1. After source qualified of B, add two filters FIL1(system_Code='University') and FIL2(System_Code='Policy') in parallel.

  2. Then use JNR1 to join A and B(FIL1) using JOINER on A.MEMBERSHIPID = B_F1.MEMBERSHIPID. Use A as detail table and use 'inner join'.

  3. Then join A and B(FIL2) using JOINER(JNR2) on A.address = B_F2.address and A.phonenumber = B_F2.phonenumber. Use A as detail table and use 'inner join'.

  4. Then join above two pipelines into one single pipeline using another Joiner(JNR3). It should be normal join and join should be primary key from table A. Get all required columns.

  5. (EXP)Then use an expression transformation. Use logic similar to below.

out_col1 = IIF( isnull(col_tableB_F1_jnr1),col_tableB_F2_jnr2, col_tableB_F1_jnr1)

Whole mapping should look like this -


SQ_TABLEB --FIL1-> -- JNR1 \
        |               |   |
        |   SQ_TABLEA --|    JNR3-->EXP.... -->TGT  
        |               |   | 
        |--FIL2-> -- JNR2  /

But i think your requirement may be like this -

A LEFT JOIN B 
ON A.MEMBERSHIPID = B.MEMBERSHIPID AND B.System_Code='University'

if yes, then change the inner join to master outer join in the JNR1 and JNR2.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • can you pls share exact error here? Also, you have to join 3 tables. You can not do it using router. however if they are in same DB you can use SQL override. – Koushik Roy Apr 16 '22 at 14:51
  • i modified the answer, – Koushik Roy Apr 19 '22 at 05:40
  • Hello, I tried your method, the error msg is: *The mapping contains blocking transformations [JNR1,JNR2,JNR3] which may cause the mapping to hang* – John Apr 20 '22 at 03:46
  • I tried same in 9.6.1 it is working. is it a warning or an error? if its warning, then go ahead. if error, then pls add a sorter right before joiner in each pipeline and sort on the keys. And in joiner pls mention sorted input. hope it will fix the error. – Koushik Roy Apr 20 '22 at 04:52
  • Could you pls log a new issue and close it? Sorry, this changed few times and if you log a new one,we can start from fresh. – Koushik Roy Apr 25 '22 at 11:32
  • 1
    Yes, here is the new post: https://stackoverflow.com/questions/72000872/how-to-do-a-joining-for-making-decision-from-2-different-filter – John Apr 25 '22 at 14:08