View previous topic :: View next topic
|
Author |
Message |
Keanehelp
New User
Joined: 27 May 2008 Posts: 71 Location: USA, CA.
|
|
|
|
There is table with following primary key
COL1
COL2
COL3
COL4
COL5
We need into insert above table.
Before inserting into table we get the value of COL4 as
SELECT IFNULL(MAX(NUM_ALRT)+1,1)
INTO WS-COL4
FROM (Same table into which we need to insert)
WHERE COL1 = WS-COL1
COL2 =WS-COL2
COL3 =WS-COL3
COL5 =WS-COL5 (Please note that COL4 is not included in the WHERE clause)
By using above mentioned query, I think we can not get any duplicate rows while inserting(as COL4 will always be different), but still I am getting -803.
Any pointers....
Also it might be relevant to know that this table is empty and it is the first time we are trying to insert into it. |
|
Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009 Posts: 365 Location: Denmark
|
|
|
|
What SQLCODE do you get from the above query when you execute it for the first record?
If you get a nonzero SQLCODE you should not use any values in the result set - they are invalid, regardless you have used the IFNULL function. That only deals with if you get a row back with NULL value in NUM_ALRT. |
|
Back to top |
|
|
Keanehelp
New User
Joined: 27 May 2008 Posts: 71 Location: USA, CA.
|
|
|
|
Thanks Kjeld for your reply.
The way exception handling is done is like
SELECT IFNULL(MAX(NUM_ALRT)+1,1)
INTO WS-COL4
FROM (Same table into which we need to insert)
WHERE COL1 = WS-COL1
COL2 =WS-COL2
COL3 =WS-COL3
COL5 =WS-COL5 (Please note that COL4 is not included in the WHERE clause)
EVALUATE SQLCODE
WHEN +0
CONTINUE
WHEN +100
CONTINUE
WHEN OTHER
MOVE 'Y' TO ERROR
After this check we try to Insert where we get -803
So I think if we getting to the Insert query we have got either 0 as SQLCODE or 100. (But in case of 100, I think result will come as NULL) |
|
Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009 Posts: 365 Location: Denmark
|
|
|
|
Keanehelp wrote: |
So I think if we getting to the Insert query we have got either 0 as SQLCODE or 100. (But in case of 100, I think result will come as NULL) |
When you get SQLCODE +100, you will not get any valid result back.
Change the code executed in WHEN +100 to assign 1 to WS-COL4, then I think it will work. |
|
Back to top |
|
|
Keanehelp
New User
Joined: 27 May 2008 Posts: 71 Location: USA, CA.
|
|
|
|
I tried running the code and found that in case no record is selected in above query the SQLCODE is still 0000(not 100) with 1 as a value in NUM_ALRT.
Thoughts????? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
1) yes, that is true : a MAX() with a where clause will return null with sqlcode +000 when no rows qualify the where-clause.
2) Are you sure the -803 is on the primary index ? maybe there is another unique index on the table.
3) Is the same transaction running several times ?
could be that tran1 is selecting max between other transaction select max() and insert.
4) Display col1 ,... col5 when inserting. maybe something else is wrong in your code. |
|
Back to top |
|
|
Keanehelp
New User
Joined: 27 May 2008 Posts: 71 Location: USA, CA.
|
|
|
|
Thanks GuyC for your reply.
I notice one more thing..
This batch program executes in 4 different region(Test,QA,UAT, Prod). It is failing in only one of them.
I compared order of Primary keys in all these environments and found that while sequence is like COL1,COL2,COL3,COL4,COL5 in 3 of them, one has sequence like COL1,COL2,COL4,COL3,COL5(Please note COL3 and COL4 are not in order).
Could this be a reason? If so then why? |
|
Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009 Posts: 365 Location: Denmark
|
|
|
|
Is the region with the disorderly index the one where the SQL is failing? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Keanehelp,
if you would use DSNTIAR to expand and describe the -803, it will tell you exactly which index you are violating.....then you can stop guessing.
if the index is Unique, hard to see why a poorly created index (columns out of order)
would be the cause of an -803.
but, have not had time to think it thru.
Regardless, you are better off determining which index was violated instead of theorising. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
select max(c4) will take longer when index is C1,C2,C4,C3,C5
thus giving the opportunity for parallel transactions to annoy eachother |
|
Back to top |
|
|
|