Varchar to Decimal conversion in db2

Question

I am trying to convert a varchar field to decimal field in db2 but I am getting an error

AMOUNT field is a varchar field which needs to be converted to decimal(31,3)

Given below is my query

SELECT CAST(ROUND(TRIM(AMOUNT),3) AS DECIMAL(31,3))
FROM TABLENAME

Note: AMOUNT field (varchar) has a NULL value as well
Sample values:

7.324088  
-42.97209  
854  
NULL  
6  
6  
350  
-6  
15.380584  
1900

I get the below error:

Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018

Answer 1

Are you sure your input values correspond with the same locale. I mean is the minus sign a minus or the MS Word minus? What about the decimal separator? In some languages there is a comma instead of a dot.

The coalesce function retrieves the first non-null value from the parameters. In the example:

coalesce (amount, 0)

If amount is null, it will return 0.

Are you sure you are passing a null value, or a string with the characters 'NULL', because in this case you have to convert the 'NULL' to '0' with the replace function.

replace (amount, 'NULL', '0')

Answer 2

Try doing like

SELECT CAST(ROUND(COALESCE(TRIM(AMOUNT),0),3) AS DECIMAL(31,3))
FROM TABLENAME