StringToDecimal Conversion issue

[mchaves]

A question to the pros....

I've got a parallel job that is reading numbers (figures) from a varchar2 field of an oracle table and converting those figures into decimal(38,2) dataset field using the function stringtodecimal within a transformer.

When I check the dataset, the column contains only values like this 000000000000.00. I suspect that it may be related to some values that are not decimals (ex 23, 45, 78 ) but it's converting all the values into 000000000000.00 including the values the are decimals (43435.45, 234000.00).

Any clues?

Thanks

[kannantechno]

Try without using StringtoDecimal function,


Source column Datatype as (Oracle) ---> Varchar2
TX ---> Decimal(38,2)
Target column Datatype as ---> Decimal(38,2)

Try the above suggestion and let me know

[zulfi123786]

Before converting a varchar to Decimal its a good practise to check if the record contains compatible values that can be converted to decimals ussing the IsValid() function. Datastage converts all non-numerics to all 0's when converting chars to decimals.

[Kryt0n]

Throw a few down a peek stage giving the before and after values. Put quotes or brackets around the before value to see if there are spaces (I'm not one to remember how the function treats spaces...)

If still not obvious, convert the before value to hex and see if any non-numeric non-display characters are lurking

[mchaves]

Hi Guys,

Thanks for the tips. They helped me to pip point the issue.

Basically, the data files had chr(13) at the end of a few fields.

So, I what I did to resolve this problem was to remove this character using the function "trim(field, char(13))" and let the transformer do an implicit conversion from varchar to decimal (38,2).

Appreciate the help from this forum.

All the best.

Mchaves

[Sreenivasulu]

Looks like a 'dos2unix' issue .

Regards
Sreeni

[chulett]

That would be true if the 'extra' pesky <CR> was part of the record terminator, however they were allegedly 'at the end of a few fields' so a different solution was required it seems.