Using a SELECT INTO Statement
Hello, just a short question concerning the usage of the SELECT-INTO-statement. In the manual it´s described that Informix supports this kind of query, but in my case it won´t work :-( I tried this statement: Select attr into table_2 from table_1 where id = 123 What is wrong? Regards, Stephan
It should read
INSERT INTO targettable
SELECT field1,field2
FROM sourcetable
WHERE ....
for Inserts where the structure of "targettable" and your selected fields
match 1:1
"Stephan Kirmse" <kirmse@fh-brandenburg.de> schrieb im Newsbeitrag
news:g25qa2$2re$1@eule.fh-brandenburg.de...
> Hello,
>
> just a short question concerning the usage of the SELECT-INTO-statement.
>
> In the manual it's described that Informix supports this kind of query,
but in my case it won't work :-(
>
> I tried this statement:
>
> Select attr into table_2 from table_1 where id = 123
>
>
> What is wrong?
>
>
> Regards, Stephan
On Jun 4, 11:21 am, Stephan Kirmse <kir...@fh-brandenburg.de> wrote:
Stephan,
Using the INTO clause as you have used it here you cannot select into
a table
Use the INTO clause in an SPL routine or an ESQL/C program to specify
the program variables or host variables to receive data that SELECT
retrieves.
The INTO clause specifies one or more variables that receive the
values that the query returns. If it returns multiple values, they are
assigned to the list of variables in the order in which you specify
the variables.
If the SELECT statement stands alone (that is, it is not part of a
DECLARE statement and does not use the INTO clause), it must be a
singleton SELECT statement. A singleton SELECT statement returns only
one row.
The number of receiving variables must be equal to the number of items
in the select list of the Projection clause. The data type of each
receiving variable should be compatible with the data type of the
corresponding column or expression in the select list.
For the actions that the database server takes when the data type of
the receiving variable does not match that of the selected item, see
Warnings in ESQL/C.
The following example shows a singleton SELECT statement in ESQL/C:
EXEC SQL select fname, lname, company_name
into :p_fname, :p_lname, :p_coname
where customer_num = 101;
The other "INTO" option would be the "INTO TEMP" clasue
SELECT ... FROM ... WHERE ... INTO TEMP
eg SELECT attr FROM table_1 WHERE id = 123 INTO TEMP table_2
Or as the other poster described use INSET INTO SELECT FROM
On Jun 4, 6:21 am, Stephan Kirmse <kir...@fh-brandenburg.de> wrote: > Hello, > > just a short question concerning the usage of the SELECT-INTO-statement. > > In the manual it´s described that Informix supports this kind of query, but in my case it won´t work :-( > > I tried this statement: > > Select attr into table_2 from table_1 where id = 123 > > What is wrong? > > Regards, Stephan SELECT-INTO-statement is an SPL statement. So it would be used in stored procedures to select values into variables for the procedure's manipulation. Informix has the standard insert into <table> select <atts> from <table2> ... syntax and the select into temp <table> syntax.
Thnx for all your answers. With your help I "solved" this "problem" Stephan bozon schrieb: > On Jun 4, 6:21 am, Stephan Kirmse <kir...@fh-brandenburg.de> wrote: >> Hello, >> >> just a short question concerning the usage of the SELECT-INTO-statement. >> >> In the manual it's described that Informix supports this kind of query, but in my case it won't work :-( >> >> I tried this statement: >> >> Select attr into table_2 from table_1 where id = 123 >> >> What is wrong? >> >> Regards, Stephan > > SELECT-INTO-statement is an SPL statement. So it would be used in > stored procedures to select values into variables for the procedure's > manipulation. > > Informix has the standard insert into <table> select <atts> from > <table2> ... syntax and the select into temp <table> syntax. >