Search code examples
sqloracle-database

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

I want to select a max value from a table:

SELECT MAX(cid) FROM itemconfiguration;

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

Solution

  • 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