Re: Embeded SQL retrieving a key from an insert

[CRPence <CRPbottle@xxxxxxxxx>]

  The given statements seemed to have been messed up.  The given 
"create table" statement was missing the "table libr/filename".  The 
given "SELECT INTO" seemed to have been mixed into\across the 
signature lines, possibly with the missing table name and table 
keyword from the CREATE in there too.  See what I mean here:
http://archive.midrange.com/cobol400-l/201002/msg00002.html

  Even after trying to reconstruct the failing statement with those 
assumptions, the number of values does not match the column list. 
Perhaps the statements can be re-posted, perhaps being sure to use 
"plain text"; i.e. perhaps the problem arose from pasting directly 
from somewhere like WDSc, versus from a re-copy of the data that had 
been pasted into a plaintext editor.?

  FWiW here is an example from V8 DB2 LUW; note also, where instead 
of using the column list on the insert to omit the IDENTITY, they 
used the keyword "default" to represent the element of VALUES which 
should always generate the identity:

   EXEC SQL SELECT ID INTO :new_id
             FROM FINAL TABLE(
               INSERT INTO company_b VALUES(
                  default, :name, :department, :job
                , :years, :salary, :benefits, :id));

  The above example is at the following link, at\beyond the text 
"SELECT statement references an INSERT statement" in its FROM clause:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/ad/samples/c/s-tbsel-sqc.htm

  If that syntax does not work, then perhaps using the function 
Identity_Val_Local will suffice.  See the following thread for a 
discussion on that function, and a\its reference to the doc for that 
scalar function, specifically for "Invoking the function within the 
VALUES clause of an INSERT statement" against another TABLE after 
the prior INSERT into an IDENTITY column:
http://archive.midrange.com/midrange-l/201002/msg00067.html
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscaidentity.htm

Regards, Chuck

Cole Gantz wrote:
> I've got a table that was created via an SQL statement that looks
> like this:
> <<SNIP create table>>
>  
> I need to get the key that is created when an insert is done
> and this is what I've pulled from a sample from a DB2 Cookbook
> but I'm getting an error in WDSc when trying to enter it:
>
>  Exec SQL
>
>    Select HDRHEADKEY
>       InTo :HeaderKey
>    From Final Table
>         (Insert InTo NotePadHd
>                   ( HdrDate, HdrTime   , HdrUser   , HdrCaption
>                   , HdrDesc, HdrInsName, HdrSubject)
>             Values(:NoteDate, :NoteTime, :NoteUser , /* value? */
>                     ' '    , :InsuredName, ' '))
>
>  End-Exec
>
>  SQL0199  Keyword TABLE not expected. Valid tokens:
>      FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE
>
> Am I trying to do something that can't be done?  Is there another
> way to get the newly created key from an insert to be used in
> another table?