SQL problem:cast from VARCHAR to TEXT

I need to loop through a recordset in a stored procedure and concatenate a bunch of misc CHAR fields, and then insert the new string into a TEXT datatype field in a separate table. My problem: if I use an unbounded VARCHAR field to total things up, I can't use it to insert into a TEXT field at the end: I get: "SQL Error (-617) = A blob data type must be supplied within this context.", and if I remove the INSERT the error goes away, so I'm pretty sure that is the issue. If I start my variable as a TEXT var, I can't concatenate CHAR fields to it. I tried to RTFM before this desperate plea, but I haven't found anything useful yet and I suppose I am over-looking something stupid. The code, if that would help (this is a dummy test case just to isolate the error, btw): CREATE PROCEDURE MNA_Agg() DEFINE chaString CHAR(32); DEFINE vcAggChar VARCHAR; INSERT INTO mna_SavedAgg(MyText) VALUES ('This works'); LET vcAggChar = ''; FOREACH AggLoop FOR SELECT myChar INTO chaString FROM MyTable ORDER BY myChar LET vcAggChar = vcAggChar || TRIM(chaString); END FOREACH; --Error here! INSERT into mna_SavedAgg(MyText) VALUES (vcAggChar); END PROCEDURE;

Cannot do it i nSPL. Make that an ESQL function. Art S. Kagel geo981010 wrote: > > I need to loop through a recordset in a stored procedure and concatenate a > bunch of misc CHAR fields, and then insert the new string into a TEXT > datatype field in a separate table. My problem: if I use an unbounded > VARCHAR field to total things up, I can't use it to insert into a TEXT field > at the end: I get: "SQL Error (-617) = A blob data type must be supplied > within this context.", and if I remove the INSERT the error goes away, so > I'm pretty sure that is the issue. If I start my variable as a TEXT var, I > can't concatenate CHAR fields to it. I tried to RTFM before this desperate > plea, but I haven't found anything useful yet and I suppose I am > over-looking something stupid. > > The code, if that would help > (this is a dummy test case just to isolate the error, btw): > > CREATE PROCEDURE MNA_Agg() > DEFINE chaString CHAR(32);> DEFINE vcAggChar VARCHAR; > > INSERT INTO mna_SavedAgg(MyText) > VALUES ('This works');> > LET vcAggChar = ''; > > FOREACH AggLoop FOR > SELECT myChar > INTO chaString > FROM MyTable > ORDER BY myChar> > LET vcAggChar = vcAggChar || TRIM(chaString); > > END FOREACH; > > --Error here! > INSERT into mna_SavedAgg(MyText) > VALUES (vcAggChar);> > END PROCEDURE;