Clarifications for StringToDecimal and DecimalToDecima

[vivekgadwal]

Hello,

I need some clarifications regarding the usage of StringToDecimal and DecimalToDecimal functions. I would really appreciate your help with regards to this. I have searched the forum on these functions, but unfortunately couldn't find anything related to my scenario...hence this post!

My scenario: My input is from a fixed width file. The amount fields are being read as Character datatypes.
Field-1: Char(3) Ex: 101
Field-2: Char(4) Ex: 1010

In the target table, which is DB2, the above amount fields are mapped to columns defined as Decimal {Col1: Decimal(7,5); Col2: Decimal(7,4)}

Field-1 ---> Col1
Field-2 ---> Col2

If I use StringToDecimal(Field-1) as a derivation into Col1 and similar definition for Col2, I am getting the warning:

Code: Select all

APT_CombinedOperatorController(3),1: Conversion error calling conversion routine decimal_from_ustring data may have been lost

APT_CombinedOperatorController(3),1: Conversion error calling conversion routine decimal_from_string data may have been lost

I have noticed that there is a DecimalToDecimal function provided and I have tried to use it, but it doesn't allow me, or rather I wasn't able to figure out, how to provide a specific Precision and Scale with this function.

Can anyone clear this confusion of mine...

Thanks.

[keshav0307]

how will 101 fit in decimal(7,5)? decimal (7,5) can be 99.99999

[vivekgadwal]

how will 101 fit in decimal(7,5)? decimal (7,5) can be 99.99999

101 in Decimal(7,5) could be (in DataStage) 01.01
In database, this is stored as 1.01000

My confusion is with the functions StringToDecimal() and DecimalToDecimal() and how to pass precision and scale to DecimalToDecimal function. I was under the impression that doing StringToDecimal on this Char field and placing it in a Decimal field should do the trick. But, if I do it, warnings are being generated.

[DSguru2B]

If you know the scale and precision, try converting it to integer, dividing it by 10 or 100 or 1000 depending upon your scale and then convert it to decimal.

[chulett]

Right, you're talking about an implied decimal here so you have to help out and divide appropriately.

[vivekgadwal]

If you know the scale and precision, try converting it to integer, dividing it by 10 or 100 or 1000 depending upon your scale and then convert it to decimal.

Thanks.

I was thinking of doing that initially, but then I came across this DecimalToDecimal function in the Guide. In the Arguments column of the help, it says:

Code: Select all

decimal (decimal) [rtype (string)] [packedflag (int8)]

So, I thought this should be the way to go as it is more robust

Any thoughts?

[DSguru2B]

Try it out and select the one that best fits your needs.

[chulett]

I don't think that DecimalToDecimal function would help here, that rtype argument is all about rounding: floor, ceil, trunc_zero, etc.

[vivekgadwal]

Hmm...then I guess the way suggested by DSGuru2B should be the only way to handle decimals then!! I will try and do that.

In the DataStage help (Guide) it was given that this DecimalToDecimal function can return a decimal value with specified precision and scale. If anyone has implemented that, please do let me know the syntax

[ray.wurlod]

There's no syntax. DecimalToDecimal() picks up metadata from the output link itself.

Of course, this makes it tricky if you want to use this function to produce an intermediate result (that is, as a nested function). In this case, evaluate it in a stage variable that has the appropriate data type (for example Decimal(5,2)).

[vivekgadwal]

Great...that was my confusion...how to mention the precision and scale. So, it picks up from the output link, huh?

Thanks Ray, Craig and DSGuru (not '2B'...already is) :D