Multiple conditions in tMap inner join

[bradsheridan]

good morning Community!  I'm looking for a little assistance on this tMap inner join criteria.  Basically it does this: if the ID field is equal between 2 tables AND the IntergerField is equal, then the join is True.  However, matching on the IntegerField requires a CASE statement: if the IntegerField is NULL and has a certain description, then return one of two values (888888 or 999999), else return the actual IntergerField value

table1.id=table2.id
AND CASE
WHEN table1.integerfield IS NULL AND table1.textfield='VALUE1' THEN 999999
WHEN table1.integerfield IS NULL AND table1.textfield='VALUE2' THEN 888888
ELSE table1.integerfield
END = table2.integerfield

Any help is greatly appreciated!  thanks

Brad

[bradsheridan]

nevermind...figured it out

[Anonymous]

Hello,

Thanks for letting us know that you have fixed this issue by yourself. We will appreciate it if you could post your solution here.

Best regards

Sabrina

[bradsheridan]

So here is the solution that worked for me.  I realized that I was trying to do all of the logic on just 1 field join between 2 tables when in fact, it really needed to be separated.

Originally in the Inner Join between table1 and table2, I put the following logic in the join expression on the ID field:

table1.id=table2.id
AND CASE
WHEN table1.integerfield IS NULL AND table1.textfield='VALUE1' THEN 999999
WHEN table1.integerfield IS NULL AND table1.textfield='VALUE2' THEN 888888
ELSE table1.integerfield
END = table2.integerfield

What I ended up doing after thinking more about it was this:

on the join expression for the ID field:

table1.id=table2.id (Simply drag and drop the ID field from table1 down to table2)

on the join expression for the integerfield:

table1.textfield.equals("VALUE1")?999999:
table1.textfield.equals("VALUE2")?888888:table2.integerfield

What I left out of my original code was to check for NULL values in the integerfield.  I did this because 1) it was redundant and 2) I couldn't figure out how to do it in my Talend expression . (suggestions are still welcome)

thanks