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.
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