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 ?

score 55 · Accepted Answer · answered Mar 18 '13 at 10:54

55

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

answered Mar 18 '13 at 10:54

RB.

35,110
12
84
126

1

`COALESCE` is preferable as it is more general (can take more than two arguments and returns the first non-NULL, while `NVL` is its special case for two arguments), it [does short-circuit](http://stackoverflow.com/a/950103/2157640) (does not evaluate arguments after the first non-NULL) and it was standardized in SQL-92 (while `NVL` is proprietary to Oracle). – Palec Sep 27 '16 at 08:07

score 6 · Answer 2 · answered Mar 18 '13 at 10:58

6

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

answered Mar 18 '13 at 10:58

zibidyum

154
1
8

score 0 · Answer 3 · answered May 14 '20 at 06:50

0

Can replace a number when max return null using ISNULL ,

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

answered May 14 '20 at 06:50

mrm aadil

1
1

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

3 Answers3

Linked

Related