Using IBM Informix database.
If I specify order_no manually, the statement works but if I try to increment it within the query, it fails.
Table structure
patkey serial
notetype char(4)
order_no int
The constraint is patkey, notetype and order_no
I can manually insert running the following and increment the order_no
insert into patnotes (patkey, notetype, order_no) values (5000, 'GEN', 3)
I wanted to increment it within the insert statement but I get a duplicate violation and don't understand how.
Here is the insert statement that I'm trying to use to increment the order_no.
INSERT INTO patnotes (patkey, notetype, order_no)
SELECT FIRST 1
p.personkey,
'GEN' as notetype,
CASE WHEN n.order_no is null then 1 else (n.order_no + 1) end as order_no
FROM
person p
LEFT JOIN
patnotes n
ON p.personkey = n.patkey
AND n.notetype = 'GEN'
WHERE
p.patid = '5000'
ORDER BY
n.order_no DESC
Checking the table the following entries exist
5000, GEN, 1
5000, GEN, 2
The next order should be 3.
Running the select portion of the above insert statement gives:
5000, GEN, 3
This is correct but fails the constraint. If I enter these manually in the first insert statement, there is no issue.
Now the next odd issue is if I clear out these entries so the first entry is order_no 1.
This will add entries for order_no 1 and 2 but then fails for 3+ but again if I specify this manually in just an insert statement, no issues.
I'm not seeing what I'm missing
Best Answer
Assuming you are using a recent 12.10 Informix version, the online documentation states:
So
FIRST
is not supported inSELECT
statements used in anINSERT
context. But usingFIRST
in the query does not give any syntax error, so maybe something else is going on.When we run the above
INSERT
we get:If we remove the
UNIQUE
constraint and run theINSERT
multiple times we keep getting the same value for columnorder_no
.It keeps inserting the same calculated
order_no
. Seems like a bug. You should open a defect with Informix support.As a workaround you can use
MAX
to calculateorder_no
:Please do note that this approach will fail miserably if you have multiple sessions trying to insert new values concurrently. You should use
SERIAL
or create aSEQUENCE
to generate theorder_no
values.