CODEWITHSUNDEEP
db2
Jhansi
Jhansi

Reputation: 9

DB2: how to resolve decfloat error for decimal column?

While trying to run below query am getting decfloat error. As per the ddl of DX.FACT_RULES table revenue is decimal(18,2)

SELECT REVENUE FROM DX.FACT_RULES

Please help me with solution.

I have tried to change as CAST(REVENUE as DECFLOAT(32)) and also CAST(DECFLOAT(REVENUE) AS DECIMAL(18,2)) still no use

Upvotes: 1

Views: 5851

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

Likley you are getting this error 

db2 "values '£101' * 1"

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

which you get when treating a string as a number, but the string can't be cast to a number (a DECFLOAT in this case)

You need to find the values that can't be converted to a number. A function such as this can be used to find such values

CREATE OR REPLACE FUNCTION DB_IS_DECFLOAT(i VARCHAR(64)) RETURNS INTEGER
    CONTAINS SQL ALLOW PARALLEL 
    NO EXTERNAL ACTION
    DETERMINISTIC
BEGIN 
  DECLARE NOT_VALID CONDITION FOR SQLSTATE '22018';
  DECLARE EXIT HANDLER FOR NOT_VALID RETURN 0;

  RETURN CASE WHEN CAST(i AS DECFLOAT) IS NOT NULL THEN 1 END;
END

e.g. 

SELECT v
FROM TABLE(VALUES ('£123'),(' +123.45 ')) as t(V)
WHERE DB_IS_DECFLOAT(v) = 0

which would return

V   
----
£123

Upvotes: 1

Related Questions