SELECT COALESCE(MAX(XXXXX) +1, 1) FROM YYYYY

Hi

I am analysing a Java code in which i have the following SQL statement

SELECT COALESCE(MAX(XXXX) +1, 1) FROM XXXXX

what does this mean.
gkmadhavAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
the statement is trying to retrieve the next available empleeid

by obtaining the current max empleeid from the table and adding 1 to it...

if no empleeid exists (NULL) then 1 is returned....

coalesce (OR value WITH SOME dbms)  RETURNS the first (from left) non null expression it encounters,,,

hth

note its potentially poor code  since if delete  are allowed  a max+1 empleeid may have already been assigned/used previously...  its usiually best to either use the DBMS's own in built identity or sequence functions or (preferably) maintain your own table of allocated reference numbers for each function.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if   MAX(XXXX) is null   COALESCE(MAX(XXXX) +1, 1)  will return 1
0
 
gkmadhavAuthor Commented:
sorry its not that clear, let me past the actual String

SELECT COALESCE(MAX(EMPEEID) +1, 1) FROM ASSIGN

there is this Assign table and EMPEEID is one the IDs.
0
 
gkmadhavAuthor Commented:
Thx for the explanation, its clear now, I am closing this question, but if possible can you please explain a bit more clearly what you are suggesting in ur last few sentences. Also i need one more suggestion, i have some big code which i would like to analyse, this is of the database connecting project where the values from excel are being read and populatedin database trying to link tables based on PID(Primary Key) and FID (Foriegn key).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.