For fun I tried the following:

INSERT INTO TIMECLOCKEVENT (ID_EMPLOYEE, EVENTTYPE, EVENTCLASS, EVENTTIMESTAMP)
VALUES (SELECT ID_EMPLOYEE FROM LIST_LINK WHERE LIST_INDEX = 7, 2, 0,CURRENT_TIMESTAMP());


in other words, I used a select statement to lookup the value for ID_EMPLOYEE from another table
(by design I am guaranteed that the select statement will return a single value of the proper type.)

Looking at the INSERT documentation, the way I read it, this is not supported.
Rather what is supported is the use of a SELECT statement to provide values for all of the fields being
inserted.

Did I stumble on an undocumented feature? I find it very useful.

Thanks

Oliver

ENGEL

>Did I stumble on an undocumented feature? I find it very useful.

I think more correctly you've stumbled onto the fact that its not possible to give examples of every different type of operation. As you're using it all you're doing is using a subselect to generate one of the values. If it didn't return a scalar by design you might be in intermittent trouble

Roy Lambert [Team Elevate]

ENGEL

> INSERT INTO TIMECLOCKEVENT (ID_EMPLOYEE, EVENTTYPE, EVENTCLASS,
EVENTTIMESTAMP)
> VALUES (SELECT ID_EMPLOYEE FROM LIST_LINK WHERE LIST_INDEX = 7, 2,
0,CURRENT_TIMESTAMP());
>
>
> in other words, I used a select statement to lookup the value for
ID_EMPLOYEE from another table
> (by design I am guaranteed that the select statement will return a single
value of the proper type.)
>
> Looking at the INSERT documentation, the way I read it, this is not
supported.
> Rather what is supported is the use of a SELECT statement to provide
values for all of the fields being
> inserted.
>
> Did I stumble on an undocumented feature? I find it very useful.

In your case it is equivalent to the simpler

INSERT INTO TIMECLOCKEVENT (ID_EMPLOYEE, EVENTTYPE, EVENTCLASS,
EVENTTIMESTAMP)
SELECT ID_EMPLOYEE,2, 0,CURRENT_TIMESTAMP FROM LIST_LINK WHERE LIST_INDEX =
7;

Where the select returns more than 1 row this will obviously insert more
than 1 row - don't know what your original query will do.

John

John, I like the equivalent statement as you formulated it below, it accomplishes the same thing and
keeps with the documentation.  That is the way will use it in my little application. Whoever gets to look
at my code, although intuitively understandable, will have less trouble if my INSERT statement
matches the documentation.

Thanks

Oliver Engel



"John Hay" wrote:

ENGEL

> INSERT INTO TIMECLOCKEVENT (ID_EMPLOYEE, EVENTTYPE, EVENTCLASS,
EVENTTIMESTAMP)
> VALUES (SELECT ID_EMPLOYEE FROM LIST_LINK WHERE LIST_INDEX = 7, 2,
0,CURRENT_TIMESTAMP());
>
>
> in other words, I used a select statement to lookup the value for
ID_EMPLOYEE from another table
> (by design I am guaranteed that the select statement will return a single
value of the proper type.)
>
> Looking at the INSERT documentation, the way I read it, this is not
supported.
> Rather what is supported is the use of a SELECT statement to provide
values for all of the fields being
> inserted.
>
> Did I stumble on an undocumented feature? I find it very useful.

In your case it is equivalent to the simpler

INSERT INTO TIMECLOCKEVENT (ID_EMPLOYEE, EVENTTYPE, EVENTCLASS,
EVENTTIMESTAMP)
SELECT ID_EMPLOYEE,2, 0,CURRENT_TIMESTAMP FROM LIST_LINK WHERE LIST_INDEX =
7;

Where the select returns more than 1 row this will obviously insert more
than 1 row - don't know what your original query will do.

John

Oliver,

<< Did I stumble on an undocumented feature? I find it very useful.  >>

No, ElevateDB will allow you to use a sub-query that returns a single column
in a single row anywhere that a normal scalar value (constant, parameter,
etc.) would be used.  In fact, in 2.04 and higher you can now have a
sub-query return multiple columns, and ElevateDB will treat the single row
as a row value constructor.  This is useful for updating more than one
column in an UPDATE statement with a correlated sub-query.  For example:

UPDATE InsertTest SET (ID,Description)=(SELECT ID,'Updated' FROM UpdateTest
WHERE ID=InsertTest.ID)

--
Tim Young
Elevate Software
www.elevatesoft.com