StringToDecimal

devsonali · Post by **devsonali** » Thu Jul 31, 2014 8:39 am

Hello All,
I have a simple test job where I am trying to convert a string (CHAR 5) into Numeric ( 5,3) oracle table

RowGen-->Transformer-->Oracle

I referred the documentation but still feel that I am doing something fundamentally wrong here

Transformation is StringToDecimal(inputfield)
Output field = Numeric (5,3) Length 5 , Scale 3

Test data has

input , output , expected output

00010 , 10 , 00.010
10010 , 0 , 10.010
33333 , 0 , 33.333

Thank you for reading this .

Mike · Post by **Mike** » Thu Jul 31, 2014 9:12 am

Looks like you have an implied decimal in your input. StringToDecimal would need an explicit decimal in the input to give you your expected result.

Mike

devsonali · Post by **devsonali** » Thu Jul 31, 2014 9:17 am

Sorry , I think i didn't get you. I am doing a string to decimal conversion so , isn't the input field suppose to be a string ?

devsonali · Post by **devsonali** » Thu Jul 31, 2014 10:54 am

I think i was able to search for a similar post (searched before but could not find it in first instance)

viewtopic.php?p=253139

I think in when I try to convert something like 33333 ,it is not possible to get the data as 5,3 as there are only 2 chars (digits) on the left of the decimal .

However is there a way to get results where 33333 gets converted into
33.333 in the output where the output column is defined as Numeric 5,3 ?

Thanks for any recommendations

chulett · Post by **chulett** » Thu Jul 31, 2014 11:12 am

As noted, because of the implied decimal you can't simple do the conversion and expect it to know what you want it to do. You'll need to do the conversion and then divide the result by 1000 to move the decimal over three places.

I seem to recall some functions allow you to specify the scale so it can do that for you automatically but this isn't one of them.

devsonali · Post by **devsonali** » Fri Aug 01, 2014 7:17 am

Thank you Craig , that works .
This resolved the issue , however , I am curious if somebody recollects the function that can allow us to specify the scale.
I will keep this post open for a while before marking it resolved.

chulett · Post by **chulett** » Fri Aug 01, 2014 8:15 am

I'm not saying that it might be anything that could help you here, just mentioned it more as an academic exercise. One example would be the Server FMT function that allows you to specify a value to "descale" when doing monetary or numeric formatting:

n[m]

n is a number, 0 through 9, that specifies the number of decimal places to display.
If you specify 0 for n, the value is rounded to the nearest integer. The output is padded with zeros or rounded to the nth decimal place, if required.

m specifies how to descale the value:
* A value of 0 descales the value by the current precision.
* A value of 1 through 9 descales the value by m minus the current precision.

If you do not specify m, the default value is 0. The default precision is 4.

devsonali · Post by **devsonali** » Fri Aug 01, 2014 11:23 am

Ok Thank you .

ray.wurlod · Post by **ray.wurlod** » Sun Aug 03, 2014 5:41 pm

The server Oconv() function with "MD"-style conversion can also specify the number of decimal places. In your example.

Code: Select all

Oconv(InLink.MaskedValue, "MD3")