string to Decimal conversion

[hargun]

hi,

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.

the job design

DB2 stage-----------xFm-----------DB2 stage

Source(apt_source varchar 255)

Target (apt_source decimal(14,2))


in xfm using

Code: Select all

StringToDecimal(select_stats.apt_source)

Please help on this .

[ray.wurlod]

Your string contains more than 14 digits. A precision of 14 in your metadata will cause this alert to be generated.

[hargun]

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.

[chulett]

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?

[FranklinE]

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.

[hargun]

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")

and getting the result 000000000000.00


Not able to get the + 000000000000.00

[FranklinE]

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.

[Mike]

Don't forget that Decimal is numeric... a zero is a zero.

000000000000.00 and + 000000000000.00 are equal numbers.

Format is irrelevant until it is viewed or written on a report.

The view data feature in DataStage just converts the internal numeric to its own default formatted string.

If you want to control formatting, you have to convert it to a string with the formatting that you want.

Mike