How to convert char into an array in Informix database?

Question

I have a stored procedure like this:

CREATE PROCEDURE Proc_Test(testvalue set(char(1000) not null))  RETURNING int;
        DEFINE z char(7000);
        LET z = ' ';

FOREACH
select * into z from table(testvalue)
END FOREACH;
return 1;
end procedure;

Due to the change in the requirement I would like to change this stored procedure as:

CREATE PROCEDURE Proc_Test(testvalue char(7000) not null) RETURNING int;
            DEFINE z char(7000);
            LET z = ' ';
    CAST(cvg AS set(char(1000)));
    FOREACH
    select * into z from table(testvalue)
    END FOREACH;
    return 1;
    end procedure;

I am having some unknown syntax error.

I know the error is here: CAST(cvg AS set(char(1000)));. Do you know better way to cast a variable to another type to convert it at runtime?

Answer 1

Puzzlement

What exactly is your procedure supposed to do? As far as I can see, it is a contorted test for 'is the string in testvalue NULL'? Maybe...Actually the code unconditionally returns 1, so it is not clear that it even does the null test, but the procedure likely executes quicker on a null string than on a string with a value.

Diagnosis

The syntax error arises because 'cvg' is not a defined variable in the procedure. Also, the CAST would have to be on the RHS of an assignment or something similar. And I'm not clear that you can break up a CHAR(7000) into seven CHAR(1000) fields in a set with a cast.

Fix?

However, I can't advise you what to do since I don't understand the old or new requirements.