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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
LowfatspreadCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.