I recently learned about the "SELECT FROM NEW/OLD/FINAL TABLE"
statement and I wanted to replace our old code, where we used
"identity_val_local()" to get auto generated values after an insert.
So I tried the following:
#sql{
SELECT
ID
INTO
:AssetEntryID
FROM
FINAL TABLE
(
INSERT INTO
Accounting.t_Debtor_Asset_Entries
(
AssetID,
MemberID
)
VALUES
(
:AssetID,
:DebtorID
)
)
};
This code works when I execute the SQL directly, and also the INSERT
statement alone is accepted by the SQLJ Precompiler. But as soon is
wrap the insert statement into the SELECT FROM FINAL TABLE statement,
it returns an SQL0530n (indicating that an RI constraint is violated),
which makes absolutly no sense in that context.
Am I doing something wrong or is this, a maybe already known, problem
of the SQLJ precompiler? If so, does there exist a workaround for it?
Regards,
Janick