SQL Question: INSERT with SELECT in VALUES section

Hi Informixers, I know it is possible to use a SELECT statement _instead_ of the VALUES section of an INSERT statement, e.g. INSERT INTO tab1 (field1, field2) SELECT field_a, field_b FROM tab2 Is there a way to combine that with the VALUES section of the INSERT statement? I need to insert a record into a table, but this record contains a field which I need to replace via a lookup in another table. I tried the following SQL which failed: INSERT INTO tab1 (field1, field2) VALUES ("nnnn", (SELECT field_b FROM tab2 WHERE field_a = "mmmm")) Is there a way to achieve this in a single SQL statement, or do I have to retrieve the value of field_b with a SELECT INTO statement first and then do the INSERT? Engine version is IDS 7.31UD5 Regards, Richard -- +-------------------------------+---------------------------------------+ | Dr. med Richard Spitz | Mail: spitz@ana.med.uni-muenchen.de | | Klinik f'r Anaesthesiologie | Tel : +49-89-7095-6110 | | Klinikum der Univ. M'nchen | FAX : +49-89-7095-6420 | | 81366 M'nchen, Germany | GSM : +49-172-8933578 | +-------------------------------+---------------------------------------+

Hi, you can try something like this: INSERT INTO tab1 (field1, field2) SELECT "nnnn", field_b FROM tab2 WHERE field_a = "mmmm" Sanja Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> wrote in message news:<bhf1cv068ua09cmk2e635uk1faabv8dlod@4ax.com>... > Hi Informixers, > > I know it is possible to use a SELECT statement _instead_ of the > VALUES section of an INSERT statement, e.g. > INSERT INTO tab1 (field1, field2) > SELECT field_a, field_b FROM tab2> > Is there a way to combine that with the VALUES section of the > INSERT statement? I need to insert a record into a table, but > this record contains a field which I need to replace via a > lookup in another table. I tried the following SQL which > failed: > > INSERT INTO tab1 (field1, field2) > VALUES ("nnnn", (SELECT field_b FROM tab2 WHERE field_a = "mmmm"))> > Is there a way to achieve this in a single SQL statement, or do > I have to retrieve the value of field_b with a SELECT INTO statement > first and then do the INSERT? > > Engine version is IDS 7.31UD5 > > Regards, Richard

Richard, I believe you can do this by selecting the value insert into tab1 (fld1, fld2) values (select "nnnn", fld_b from tab2 .......)) Confess to not having tested this recently Tim