Re: Including 4GL program variable in selection list of SELECT statement?

Richard Spitz <richard.spitz@ana.med.uni-muenchen.de> wrote: :Hi Informixers, : :is it possible to include a 4GL program variable in the selection list :of a SELECT statement? : :In a 4GL program (4.10, ancient, I know...) I'm trying to do the :following: : :DEFINE p_variable CHAR(4) : :LET p_variable = ... : :INSERT INTO some_table : SELECT a.field1, b.field2, p_variable, c.field3, c.field4 : FROM table1 a, table2 b, table3 c : WHERE ... : :This does compile, but gives me a runtime "-201 Syntax Error". : :Any suggestions on how I might achieve this? This should be simple enough using prepare and execute like this: define p_variable char(4), p_statement char(2000) # Or whatever size you need let p_variable = ... let p_statement = "insert into some_table", " SELECT a.field1, b.field2, '", p_variable clipped, "', " c.field3, c.field4", " FROM table1 a, table2 b, table3 c", " WHERE ..." prepare s_statementname from p_statement execute s_statementname with the appropriate error handling added. If the ' don't work in your version you may be able to write \\", "" or use a char(1) variable initialized to ascii(34) (the code for "). : :Regards, Richard : :PS: The way I'm working around this now is replacing the variable : with a constant, e.g. "N", and then doing an UPDATE of all : rows where this field is "N". Not too elegant, but it works. : Of course, the INSERT and UPDATE statements are wrapped in a : transaction. :-- :+--------------------------+------------------------------------------+ :| Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | :| EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3413 | :| Klinikum Grosshadern | FAX : +49-89-7095-8886 | :| 81366 Munich, Germany | | :+--------------------------+------------------------------------------+ Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org