LPAD amount with -sign in Informatica

[bbn2004]

Hello friends, Somone has already asked similar question in the forum. But mine is slightly different.I have to covert the amount value to fixed length(17,2) prefixed with 0's in the output. So, if I get 125.52, the output should be '00000000000125.52' This is working fine for non-negative value. If the amount is -ve, then my output should be -00000000000125.52. That's where I have problem.

I used the following condition in my output port in the expression transformation:
iif(bill_amt<0,'-'||lpad(to_char(bill_amt*-1,16,'0')),lpad(to_char(bill_amt),17,'0'))

But I get the error, FATAL ERROR : Caught a fatal signal/exception.

I am using INFA PC ver6.1. so my problem is concatenating with - sign in the output.
Note: the following sql using the same logic is working fine
(oracle is my DB)

select bill_amt, '-'||lpad(to_char(bill_amt*-1),16,'0') from bill_main
where bill_amt < 0;

Any suggestions? Thanks in advance.

BN

 

[blom0344]

iif(bill_amt<0,'-'||lpad(to_char(bill_amt*-1,16,'0')),lpad(to_char(bill_amt),17,'0'))

Why multiply with -1 if you are going to concatenate the '-'sign in front of the string? Seems to me one action too many.

Anyway, do not make the mistake that INFA's functions are always identical with RDBMS functions (such as ORACLE). Some are, others are not...

Ties Blom
Information analyst

 

[vaass]

This is working fine with

IIF(AMOUNT>0,LPAD(to_char(AMOUNT),10,0),'-'||LPAD(Substr(to_char(AMOUNT),2,length(AMOUNT)),10,0))