How to implement multiple case statement in Tmap

[AAA999]

Talend Gurus,

can anyone let me know how to handle multiple case statement in Tmap?

nvl(CASE WHEN regexp_instr(F1,'/|,| ') = 0 THEN
REPLACE(F1,' ')
ELSE substr(F1,1,regexp_instr(F1,'/|,| ')-1)
END
,F1)
as X1,

CASE WHEN regexp_instr(F1,'/|,| ') = 0 THEN NULL ELSE
REGEXP_REPLACE(SUBSTR(F1, regexp_instr(F1,'/|,| ')),'/|,|')
END AS X2,

(CASE WHEN INSTR (F1,'@',1,2) = 0
OR F1 LIKE '%<%' OR F1 LIKE '%>%' OR UPPER(F1) LIKE '%IN%' OR LENGTH(F1) >= 10
THEN 'Y' ELSE 'N' END) AS Y,

(CASE WHEN INSTR (F1,'@',1,2) = 0 THEN 'A'
WHEN F1 LIKE '%<%' OR F1 LIKE '%>%' THEN 'C'
WHEN UPPER(F1) LIKE '%IN%' THEN 'CI'
WHEN LENGTH(F1) >= 10 THEN 'L'
END) AS Z

Thanks

[akumar2301]

https://community.talend.com/t5/Design-and-Development/Using-local-variable-in-tmap-expression-build...

[AAA999]

I have multiple substr and instr also in my case statement. I wrote the code in oracle, how to write in talend

[akumar2301]

https://help.talend.com/reader/VXLHZvns6nwhPGeQbm1Iig/2PD0dBEfYwKBhZpu3o4vMA

also StringHandling routine has function for instr and substr

[AAA999]

Gurus,

Can anyone let me know what is the function in talend for comparing 2 dates?

greatest (dt1, nvl(dt2,dt1)) as date.

Thanks

[AAA999]

Still not resolved. is there any function available rather than writing a routine for this solution?

[akumar2301]

Can you please write your user story ? It’s complicated to read sql
statements.

Which functionality you are not able to achieve w/o user defined routines ?

[AAA999]

I am trying to achieve this using tmap. I have multiple case statement with substring, instring, regular expressions(Regexp_Instr, regexp_replace, Rexexp_like) inside the case statement. I couldn't find a function that can be used to achieved using the rule defined. I am not sure why one needs to write a routine to achieve it.

If the source data has delimiters like ,'/|,| -' then replace it with space.
and capture the value before the space
tom ; tim
john , jack
jill/// john
sandy - june
linda

result
tom
john
jill
sandy
linda

nvl(CASE WHEN regexp_instr(F1,'/|,| ') = 0 THEN REPLACE(F1,' ')
ELSE substr(F1,1,regexp_instr(F1,'/|,| ')-1)
END
,F1)
CASE WHEN regexp_instr(F1,'/|,| ') = 0 THEN NULL ELSE
REGEXP_REPLACE(SUBSTR(F1, regexp_instr(F1,'/|,| ')),'/|,|')
END AS X2,

[akumar2301]

it should quite easy to do this with tExtractRegexField component .

Try with regex "([a-zA-Z]+)(.*)"

it should give you 1st field.

[akumar2301]

It works . Please try. you will get your expected result.