Invalid Decimal Output

[ds_search2008]

Hello all,

I have a question regarding varchar to decimal conversion

I have 3 decimal fields in the target table. I'm extracting all these three decimal values from input VarChar field.

Input values:
InpCol1 -23.765980
InpCol2 - 47.987650
InpCol3 - 2101.00000

I'm using the following derivations in transformer

Code: Select all

StringToDecimal(Field(lnkinpData.ProdPriceX,',',1))

Code: Select all

StringToDecimal(Field(lnkinpData.ProdPriceY,',',2))

Code: Select all

StringToDecimal(Field(lnkinpData.ProdPriceZ,',',3))

Output Values Target DataType

.000000000 Decimal[9,9]
.000000000 Decimal[9,9]
00002101.00000 Decimal[13,5]

I searched this forum and tried using Double instead of Decimal. However I got all three output values as .000000. I'm trying to extract the exact input decimal values (23.765980, 47.987650, 2101.00000...)

Kindly let me know your idea on this.

Many thanks and Kind Regards

[ray.wurlod]

The Field() functions don't do anything (there is no "," in the source strings), or is there something extra you're not telling us?

What are the sizes of the three Decimal fields in the target? Have you configured default values for them? Are they nullable? Do you allow "all zeroes" for decimal numbers?

[chulett]

I wondered the same thing about the role of the Field() function here until I saw this where (I assume) all three fields are being pulled from a single string field:

I'm trying to extract the exact input decimal values (23.765980, 47.987650, 2101.00000...)

[ray.wurlod]

Good catch. So a Trim() might also be indicated.

[ds_search2008]

Ray and Craig,

Thanks a lot for your valuable time and replies.

Currently the input is single column having all the three values in the format
"23.765980","47.987650","2101.00000"

Craig you are right. All these three values are being extracted from a single input field and mapped to three different target columns.

Code: Select all

StringToDecimal(Trim(Field(lnkinpData.ProdPriceX,',',1),'"'))

The length of the three target decimal columns are:

ProdPriceX - [9,6]
ProdPriceY - [9,6]
ProdPriceZ - [13,5]

Expected Output
23.765980
47.987650
2101.00000

Actual Output
.000000000
.000000000
00002101.00000

Kindly could you please let me know if the derivation is incorrect and what should I do to get the correct result.

Many Thanks & Kind Regards

[sbass1]

Perhaps this will work?

iconv(YourVar,"MD2") / 100
iconv(YourVar,"MD3") / 1000
iconv(YourVar,"MD4") / 10000

etc, depending on the number of decimals you want.

See the examples for iconv in the Server Job doc.

HTH...

[ds_search2008]

sbass1, I'm currently working on parallel jobs.

[ray.wurlod]

What happens if you make PriceX and PriceY larger, for example decimal[11,6]?

[ds_search2008]

Thanks a lot Ray.

Actually I tried using a stage variable as
GetDcmlVal - Varchar 20
Stage Variable Derivation: Trim(Field(lnkinpData.ProdPriceX,',',1),'"')

Output link derivation: StringToDecimal(GetDcmlVal)

I'm getting the expected output.

sbass1 - for your kind information iconv /ovonv function do not work in parallel version 8x.

Many Thanks & Kind regards for all your valuable inputs and time.