CODEWITHSUNDEEP
sqloracle-database
Mariusz Jamro
Mariusz Jamro

Reputation: 31643

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?

Upvotes: 30

Views: 38092

Answers (3)

mrm aadil
mrm aadil

Reputation: 11

Can replace a number when max return null using ISNULL ,

ISNULL(MAX(cid),0) FROM itemconfiguration;

Upvotes: 1

zibidyum
zibidyum

Reputation: 174

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

Upvotes: 7

RB.
RB.

Reputation: 37182

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

Upvotes: 62

Related Questions