Re: Insert/Select question in 4gl

On Tue, 8 Sep 1998, Octav Chiriac wrote: > Mickey Mestel wrote: > > i'm at a customers where in a 4gl program they are doing something like > >this: > > > >insert into table1 col1,..., name, ...coln > > values (select ..., first_name + last_name, ...from table2)> > > This is WRONG in 4GL. You should concatenate the first and last names > in 4gl program: > Let full_name = first_name CLIPPED, ' ', last_name > > and then insert full_name into table1 Octav is correct. There are two forms of the INSERT statement: INSERT INTO Table VALUES(value1, ...) INSERT INTO TABLE SELECT ... FROM ... WHERE ... The statement in Mickey's question combines features of both, with the inevitable result that it does not work. There are a number of constraints on what you can specify in the VALUES list, and expressions such as string concatenation or arithmetic are not allowed. Hence, treated as a single-row INSERT, the original syntax is wrong and you must follow Octav's suggestion. Alternatively, you can do the string concatenation using the '||' operator, and you will drop the VALUES symbol and the two parentheses. However, I4GL does not recognize the '||' as valid SQL syntax, so you will have to prepare the statement and then execute the prepared statement. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn