= Incrementing a max value
-
Hi all, Let me supply the Code and then explain:
DECLARE @vchRequestNumber Integer SELECT @vchRequestNumber = MAX(CONVERT(INT,(ISNULL(vchRequestNumber, 0))))+1 FROM tblRequestOk, here's the deal.. What works: IF vchRequestNumber is GREATER THAN 0 in the TABLE, the code WORKS.. and therefore I am presented with a value of vchRequestNumber PLUS 1. What Doesn't work: IF vchRequestNumber is NULL, then a ZERO replaces it(which is fine); BUT it is returning 0 instead of 0 PLUS 1. Anyone has any ideas??? -
Hi all, Let me supply the Code and then explain:
DECLARE @vchRequestNumber Integer SELECT @vchRequestNumber = MAX(CONVERT(INT,(ISNULL(vchRequestNumber, 0))))+1 FROM tblRequestOk, here's the deal.. What works: IF vchRequestNumber is GREATER THAN 0 in the TABLE, the code WORKS.. and therefore I am presented with a value of vchRequestNumber PLUS 1. What Doesn't work: IF vchRequestNumber is NULL, then a ZERO replaces it(which is fine); BUT it is returning 0 instead of 0 PLUS 1. Anyone has any ideas??? -
Hi Dayekh, try this:
DECLARE @vchRequestNumber INT SELECT @vchRequestNumber = MAX(CONVERT(INT,(ISNULL(vchRequestNumber, 0)))) FROM tblRequest SET @vchRequestNumber = @vchRequestNumber + 1Greetings Ralphhehehe.. i tried that exact thing right now.. but it doesn't work... I found the problem tho.. when no records exist, i.e. when vchRequestNumber is NULL, the ENTIRE evaluation is returning a NULL... so this is what I had to do: ISNULL(MAX(CONVERT(INT,(ISNULL(vchRequestNumber, 0)))),0)+1 Thank you for the reply!
-
hehehe.. i tried that exact thing right now.. but it doesn't work... I found the problem tho.. when no records exist, i.e. when vchRequestNumber is NULL, the ENTIRE evaluation is returning a NULL... so this is what I had to do: ISNULL(MAX(CONVERT(INT,(ISNULL(vchRequestNumber, 0)))),0)+1 Thank you for the reply!
Alternatively, you could use
COALESCE(vchRequestNumber, 0) + 1Deja View - the feeling that you've seen this post before.