By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

select from final table and sqlj

P: n/a
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

Mar 14 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Mar 14, 7:21 am, "Janick Bernet" <jaber...@gmail.comwrote:
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
Forgive me for asking a potentially stupid question--I intend no
insult--but are you sure that the Accounting.t_Debtor_Asset_Entries
record that you're trying to insert isn't missing a parent in some
other table, thus throwing the RI message?

--Jeff

Mar 14 '07 #2

P: n/a
Grml. Google says there are two messages, but I can only see my own!? :
(

Mar 15 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.