Converting Decimals and stripping of dots

[Harini]

All,

I am trying to do the below sql thing in Datastage(transformer). I read my source Amount column as Char(16) though the format of the value is (Decimal 15,2). The same column is used to populate both these target columns.

CAST(ABS(CAST(PREMIUM_WRITTEN_AMT as DECIMAL(15,2))*100) as DECIMAL(15,0)) PREMIUM_WRITTEN_AMT_HASH,

cast(PREMIUM_WRITTEN_AMT as Decimal(15,2)) as decimal(15,2)) as PREMIUM_WRITTEN_AMT_FIN

I created a Stage variable which would convert "-." and in the actual column derivation I converted it again to decimal using StringToDecimal function. In the length column in the transformer output I specified 15 and scale of 0. However, this doesn't seem to yield the expected results. I am not able to get rid of the . from the output.

Eg:

Amount = 0000000001326.36-
PREMIUM_WRITTEN_AMT_HASH = 132636
PREMIUM_WRITTEN_AMT_FIN = 1326.36


Amount = 0000000005668.77
PREMIUM_WRITTEN_AMT_HASH =566877
PREMIUM_WRITTEN_AMT_FIN =5668.77

thanks,
Meenakshi

[vinothkumar]

Change the output column data type as INTEGER in transformer and see..

[ray.wurlod]

For the HASH values Trim the leading zeroes and the trailing sign.

For the FIN values divide the HASH values by 100.00.