Update a column values base on the vakues of another column in the same table using tmap

[Anonymous]

Hello,

I cannot deternine on how can I uodate the values of a column base on the values from another colum on the same table using talend tmap.

in typical mysql I can make something like
update tbl set col2 = case
when col1 = 'Nice' then 'N',
when col1 = 'bad' then 'B',
when, col1 = 'Good' then 'G' ;
End

How can I do this using tmap, any help is realky and highly appreciated. Cheers

[TRF · Accepted solution]

You have to use ternary notation for the target field in your tMap:
"Nice". equalsIgnoreCase(row1.col1) ? "N" : "bad".equalsIgnoreCase(row1.col1) ? "B" : "Good".equalsIgnoreCase(row1.col1) ? "G" : "?"

Should work.

[Anonymous]

Thanks for the response, but what is tenary notation? Should I just put this on the expression for the target column that should be updated or?

[Anonymous]

And actually I want to base the values of the column that needs to  be updated base on the values from another column on the same table. Is this will work with that?

[TRF]

Yes, just put the expression for the target field.
The ternary notation or operator is here to build expressions for the right part of a variable setting where you cannot write an if-then-else expression. That's exactly the situation for a tMap.
You can find a lot of information if you search on Google or other. Here is an example https://alvinalexander.com/java/edu/pj/pj010018

[Anonymous]

Thank you very much!

[TRF]

You're welcome

[Anonymous]

Hi all,

In my file i will get two columns Column_A & Column_B such that only one of the value is NULL

I need to map the above column values to my column in db called VALUE.

I am using ternary operation in Tmap such that i need to populate only one the value from two columns giving preference to column_A.

My Logic:

"".equalsIgnoreCase(row19.Column_A)?("".equalsIgnoreCase(row19.Column_B)?null:row19.Column_B):row19.Column_A

Above logic is not working properly, when Column_A is null, it should be populating Column_B which is not NULL, but it is populating as NULL.

Please help me.

[akumar2301]

Null and empty string is two diff stuff so null doesn’t match to “”.

(Col.a == null || “”.equal...)?( Col.b == null || “”.equal...)? ....

[Anonymous]

Thanks for the suggestion Abshishek,

Below is my logic in Tmap:

(row19.questionAnswerFreeText == null || "".equalsIgnoreCase(row19.questionAnswerFreeText))?( (row19.questionAnswerText) == null || "".equalsIgnoreCase(row19.questionAnswerText)

?null : (row19.questionAnswerText)) : row19.questionAnswerFreeText

Still its not working.