CODEWITHSUNDEEP
informatica-powercenter
Gary
Gary

Reputation: 1

Convert number to currency using Informatica

I'm trying to figure out the best way to convert numbers into currency using Informatica. For example, I want to convert 4000 to $4,000.00. I think the Java tx is the way to go but I have never used it and I'm unable to find examples of how to code it properly so that it works. Any help you can provide would be greatly appreciated.

Thank you,

Gary

Upvotes: 0

Views: 1398

Answers (1)

Samik
Samik

Reputation: 3455

Here is a solution in Expression Transformation without using Java. Of course, this is kind of static, but you can always code it for maximum possible amount.

v_AMT_STR := TO_CHAR(TO_DECIMAL(TO_CHAR(inp_AMT),2)) 

v_LEN := LENGTH(v_AMT_STR) 

v_THSND_SEP := DECODE(TRUE,
    IN(v_LEN,7,8,9), SUBSTR(v_AMT_STR,0,v_LEN-6)||','||SUBSTR(v_AMT_STR,-6),
    IN(v_LEN,10,11,12), SUBSTR(v_AMT_STR,0,v_LEN-9)||','||SUBSTR(v_AMT_STR,-9,3)||','||SUBSTR(v_AMT_STR,-6),
    IN(v_LEN,13,14,15), SUBSTR(v_AMT_STR,0,v_LEN-12)||','||SUBSTR(v_AMT_STR,-12,3)||','||SUBSTR(v_AMT_STR,-9,3)||','||SUBSTR(v_AMT_STR,-6),
    v_AMT_STR) 

o_CURRENCY := '$'||v_THSND_SEP

inp_AMT is decimal input port

v_ and o_ are variable and output ports respectively

It supports up to $999,999,999,999.99. You can add more lines in the DECODE above to go beyond that.

Upvotes: 1

Related Questions