Re: Insert/Select question in 4gl

Jonathan Leffler wrote: > 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. Incidentally, you'll probably find the second way considerably faster than retrieving each row to the front-end, concatenating, and passing the results back to the engine. June -- june_t@hotmail.com Lost in the wilds of Palo Alto, living on Peanut M&M's