Re: SQL problem on set ( repl )

On Mon, 1 Dec 1997, pencoke wrote: } I find some simple job that can not work with SQL command,and need to } write 4gl to convert data. } } My task is to repl every row with a Newly added char } } i.e. table abc } 5 char 5 char } xyz xyz } |-----| --> |-----| } old change New } a001 to ba001 } a002 ba002 } a003 ba003 } } I use : update abc } set xyz = 'b'+xyz[1,4] } where ... ... } } However, what I get is a series of 0.00 !!! Not very surprising; the string concatenation operator is '||' not '+'. So you should be trying: UPDATE Abc SET Xyz = 'b' || Xyz[1,4] WHERE ... This will work in DB-Access and related products, but you can't write it like that in I4GL -- it doesn't know about the concatenation operator and would give you a syntax error at compile time. You can, however, prepare and execute the statement: LET upd = "UPDATE Abc SET Xyz = 'b' || Xyz[1,4] WHERE ..." PREPARE p_upd FROM upd EXECUTE p_upd FREE p_upd Adding 'b' to another non-numeric field is likely to produce rubbish. } I tried other combination, but get no success. See above. } Finally, I need to write a 4gl to convert. Not really. } Does anyone has any suggestion to do just in the SQL command environment See above. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>