DECFLOAT Error While running Procedure in DB2

Question

I am executing the following procedure in DB2.

create procedure checkUpdate()
LANGUAGE SQL
begin 
DECLARE EOF INT DEFAULT 0;
DECLARE STMT VARCHAR(200);
DECLARE COL_NAME NVARCHAR(40);
    Declare col_name_cursor cursor  for 
    select c.column_name from sysibm.tables t join sysibm.columns c on t.table_schema =   c.table_schema and t.table_name = c.table_name where t.table_schema ='ARCHANA' and t.table_name =   'ADULT'  and c.data_type not like 'CHARACTER%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF = 1;
    OPEN col_name_cursor;
    WHILE EOF = 0 DO
    FETCH FROM col_name_cursor INTO COL_NAME;
    INSERT INTO GAUSSIAN_VALUES select CLASS,avg(COL_NAME),stddev(COL_NAME) from ADULT GROUP BY CLASS;
    END WHILE;
    CLOSE col_name_cursor;
    end@

However, I get the following error while executing this on the DB2 command window.

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

I did some debugging to find that the error comes from the Declare cursor line. I changed the select statement for the cursor to "select age from adult" for checking and the error disappeared. The error does not occur when integer values are selected. Occurs in the case of varchar. I am stuck and do not know what is the link missing here.

Thank You!

What are you trying to do here? If you're attempting to average all values in whatever column you pulled, you'll actually need dynamic SQL - as it is, it's attempting to average a constant - whatever string COL_NAME contains (which is why you're getting the error). What's the point of inserting the single FEATURE value on a different row? Keep in mind that tables are unordered sets, meaning the two rows you just inserted aren't next to each other... — Clockwork-Muse
@clockwork-Muse Yes, I I am attempting to average values in the column I pulled. Thank you for the suggestion. I did not know about dynamic sql. The feature insertion was just to test. Forgot to remove that from the code. Sorry. Thank You. — Tazo

Charles Charles · Accepted Answer · 2014-12-08T13:47:35

COL_NAME is a NVARCHAR type...

How can you possibly calculate avg(COL_NAME),stddev(COL_NAME) of a character value?

Perhaps you meant to take the length of it?

DECFLOAT Error While running Procedure in DB2

1 Answers