How to treat MAX() of an empty table as 0 instead of NULL

Question

I try to select max value from table

SELECT MAX(cid) FROM itemconfiguration; 

However when table itemconfiguration is empty the MAX(cid) statements is evaluated to NULL while i need a number. How to handle this and treat NULL as 0 ?

Answer 1

Just use Coalesce or NVL to handle NULLs.

The following code will return 0 if MAX(cid) is NULL

SELECT COALESCE(MAX(cid), 0) FROM   itemconfiguration 

Answer 2

SELECT NVL(MAX(cid), 0) FROM itemconfiguration;