topic Multiple conditions in tMap inner join in Talend Studio

Multiple conditions in tMap inner join

bradsheridan — Mon, 22 Oct 2018 16:52:55 GMT

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

Re: Multiple conditions in tMap inner join

bradsheridan — Mon, 22 Oct 2018 18:26:52 GMT

nevermind...figured it out

Re: Multiple conditions in tMap inner join

Anonymous — Tue, 23 Oct 2018 04:01:36 GMT

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

Re: Multiple conditions in tMap inner join

bradsheridan — Tue, 23 Oct 2018 12:49:26 GMT

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:

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