Page 1 of 1
Invalid Decimal Output
Posted: Mon Apr 13, 2009 8:49 am
by 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
Posted: Mon Apr 13, 2009 2:24 pm
by 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?
Re: Invalid Decimal Output
Posted: Mon Apr 13, 2009 2:39 pm
by 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:
ds_search2008 wrote:I'm trying to extract the exact input decimal values (23.765980, 47.987650, 2101.00000...)
Posted: Mon Apr 13, 2009 2:40 pm
by ray.wurlod
Good catch. So a Trim() might also be indicated.
Posted: Wed Apr 15, 2009 2:47 am
by 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
Posted: Wed Apr 15, 2009 4:42 am
by 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...
Posted: Wed Apr 15, 2009 5:15 am
by ds_search2008
sbass1, I'm currently working on parallel jobs.
Posted: Wed Apr 15, 2009 1:42 pm
by ray.wurlod
What happens if you make PriceX and PriceY larger, for example decimal[11,6]?
Posted: Thu Apr 16, 2009 5:06 am
by 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.