Varchar To Decimal Conversion In Db2

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

Solution 1:

Try doing like

SELECTCAST(ROUND(COALESCE(TRIM(AMOUNT),0),3) ASDECIMAL(31,3))
FROM TABLENAME

Solution 2:

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