i have input column apt_source(varchar 255) having records like "+ 000000000000.00" and needs to covert to target datatype decimal(14,2) but getting errors
APT_CombinedOperatorController,1: Conversion error calling conversion routine decimal_from_string data may have been lost.
increase the length in target DECIMAL(19,2) still getting the same error
APT_CombinedOperatorController,1: Conversion error calling conversion routine decimal_from_string data may have been lost.
The data from source is coming as "+ 000000000000.00"
i have doubt about "+" sign coming with records . Is Decimal accepted these.
I'm assuming it's more about the size of the originating string (255) rather than the data itself. If you drop the string size down to match the data, does that change the message?
-craig
"You can never have too many knives" -- Logan Nine Fingers
There is no doubt in my mind that "data may have been lost" is the problem. This is a truncation error.
Tranform the Varchar(255) to a Char(15) first, you need to include a position for the sign unless you want to drop it, using Trim. That should remove the error. You can also use that to remove spaces like between the sign and the first digit. A "clean" Char with the same length as the Decimal -- I'd use 15,2 to leave room for the sign -- column is the only way to avoid errors and warnings.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
i have tried again by using the function given below and also by transfrom varchar(255) to char(15) but getting same result as getting by the functions below
StringToDecimal(select_stats.AVG_NET_PAR_BAL_C[14,2]) .
Also tried by StringToDecimal(select_stats.AVG_NET_PAR_BAL_C[14,2],"ceil")
Can you examine the actual raw output data as it is stored on disk? You haven't told us the character set you are using, and I'm sure about this in EBCDIC only, but storage of the sign does not dictate how it is displayed.
If your storage format has an explicit position for the sign, you can verify that the number is formatted as positive. That the "+" does not display is a system, cosmetic thing, not necessarily indicating that you have lost the sign. If your storage format has that explicit position but it is not occupied, only then can you be sure that you have lost the sign from the input source.
Also, are there any warnings on your latest coding attempt? They might indicate to you whether or not your sign is being truncated.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson