Varchar (SQL Server) to Decimal (Oracle) conversion

[Childrensmn]

I am pulling ClaimAmt from SQL Server and the datatype is Varchar(30). I am loading this into oracle table with Decimal (38,2) datatype with 2 decimal places.

I used StringToDecimal function and Implicit conversion in the transformer but it does not work the way I want to see. It does not put the 2 decimal places. I have also tried to set Varchar to 38,2 to make sure source and target have same length.. no good.

Please see examples below..

Source: 25.25
Target: 25

Source: 155.00
Target: 155

Any help is appreciated. Thanks!!

[ray.wurlod]

Did you include a value of 2 for Scale in the metadata? Have you specified a format string in your StringToDecimal() function call?

[Childrensmn]

Thanks for the reply.

Yes, I have set the scale to 2 in the datastage job. The SQL Server source column data type is Varchar(30).
Could you please elaborate on what you mean by format string in StringToDecimal().

Here are the things I have tried:
Implicit conversion: Truncates the decimal places (Oracle datatype is Decimal 38 with scale of 2)
Source: 155.25
Target: 155
StringToDecimal() with datatype 38 scale of 2
StringToDecimal() with datatype 30 Scale of 2 (to match with source)

Am I missing anything?

Thanks!!

[chulett]

I think Ray may be thinking of StringToDate rather than StringToDecimal, the former needs a format string to know where all of the date/time components are, the latter doesn't need one. It does, however, support an optional rtype argument if there is any type of rounding that needs to occur. You're not using any of those are you?

Clarify again for us the column in the job that the function is populating, saying "datatype 30" isn't accurate - what is the actual data type, precision, and scale? And your Oracle target field is a NUMBER(38,2) is that correct?