Issue with TOP and Coalesce(Max)

Database
Enthusiast

Issue with TOP and Coalesce(Max)

Hi All,

I have a table which has an ID field, that gets autoincremented when we enter any value. To insert any value in this table we are using a Stored Procedure, which will pass the parameters that needs to be inserted and the procedure will autoincrement the ID value. So for the same we have coded as below:
INSERT INTO IBNR_WR.UI_SUM
(
SUM_ID
,SUM_NM
,SUM_DESC_TXT
,UPD_DT_TM
)
SELECT TOP 1
COALESCE(MAX(SUM_ID),0)+1
,prm_SUM_NM
,prm_SUM_DESC_TXT
,CURRENT_TIMESTAMP
FROM IBNR_WR.UI_SUM;
Thus from the above it selects the max/top value of the ID from the table and increment it by 1 and then insert the new values. But the above query does not work fine when the table is empty or when we want to insert 1st row of the table, though we have mentioned the coalesce function, but it is not working.
When i remove the TOP1 from the query and run it on the empty table it again starts working fine..i.e. it takes care of the 1st row to be inserted into the table.
Do let me know what is the reason behind this behavior...is TOP1 overiding the coalesce property of the query?
1 REPLY
Senior Apprentice

Re: Issue with TOP and Coalesce(Max)

I don't know, why it's returning nothing, i just know it's stupid to mix TOP 1 and MAX, because both do the same.

Maybe it's a bug, but why trying to find that out, if you just remove the TOP and it's working?

Dieter