The issue is not about LPAD or RPAD but how to identify a number and then a decimal and then we have to pad zeros. Any idea or suggestion?
assumption is that INPUT is a decimal
lpad(to_char(to_integer(abs(INPUT)*100)),12,'0') || iif(INPUT<0,'-','')
Easy to do, but due to the negative sign following the actual number you should use two variable ports in an EXP to make life easier.
Start with a variable port v_val100 with the following expression (assuming INPUT is never NULL):
Abs( Input * 100.0)
Next a variable port v_is_neg of type Integer with this expression:
Input < 0.0
(that's a shortcut for "IIF( Input < 0.0, TRUE, FALSE)")
Now the output port:
LPad( To_Char( v_val100, 12, '0') || IIF( v_is_neg, '-', '')
Thank you Nico. I used the format multipliers in oracle to do this. But I will try to implement in Informatica and see how the data flows. Thanks again. you rock.
happy to help whenever I can. ;-)
Honestly I would definitely try the PowerCenter version as well, and then I would be interested to see which one is faster. Please let us know what you find.
Thank you Nico. Both are effective and fast. I didn't find much difference in execution. I tested in both way. Kudos to you for bringing that up in Informatica. You are awesome.
sorry for being so pernickety, but you shouldn't use TO_INTEGER() here (this function returns an Integer which cannot hold 12 digits). You can, however, use TO_BIGINT() here:
LPad( To_Char( To_Bigint( Abs( input) * 100)), 12, '0') || IIF( Input < 0, '-', '')
The reason why I haven't used this single expression is that with two variable ports it's easier to change the data types or lengths or whatever.
Thanks and regards,