Varchar to Decimal conversion issue in BODS4.2

dataservices job design
1

I have a input field input_col of data type VARCHAR(17) which is to be loaded into the field of data type DECIMAL(16,8).

I used the BODS function: to_decimal_ext(input_col,’,’,’.’,16,8)

Decimal Sep: Comma
Thousands Sep: Dot

But when the job runs it is failing due to Numeric value out of range error.

eg: if the input value given is : 1.658
The job fails giving the below error.

Failed in “out_col_name” column with the value 165800000.00000000.

Can someone please tell me why is BODS generating 165800000.00000000 which is of the precision/scale of (17,8) instead of 1.658,00000000

Is it because I am using DOT “.” as thousands seperator in input but as thousands seperator in output.Will this be an issue.

Thank You

MaveriK (BOB member since 2012-02-26)

2

Have you tried cast function?

Cast (colname, ‘decimal(16,8)’)

Arun.K :us: (BOB member since 2011-10-18)

3

The input has decimal separator as dot and thousands separator as comma. While the output has vice versa. How can I modify it using cast?

MaveriK (BOB member since 2012-02-26)

4

Will like to know if you have found the solution…

BOBJFan (BOB member since 2011-09-24)

5

Try this

cast(replace(‘col_name’,’,’,’.’), decimal(16,8))

Arun.K :us: (BOB member since 2011-10-18)

6

Thank you Arun. The conversion runs successfully with the cast option you have suggested.
But is there any reason why the function to_decimal_ext is not doing the intended conversion and causing errors.

MaveriK (BOB member since 2012-02-26)

7

Have you tried with to_decimal instead of to_decimal_ext()? Because the to_decimal_ext() will add precision as parameter.

Arun.K :us: (BOB member since 2011-10-18)