NVL Function in SQL

Question

what excatly 1 - NVL(up, 0) down this statement mean , is it giving me the contradiction of up ?

NVL returns `UP` if it is not null, else zero. So if UP is not null it will be subtracted from 1. — upog, May 05 '20 at 13:00
i see this line in a query where it should give me sum of down machines , soon after this i have sum(down) . what is the purpose of subtracting here — Shwetha Shetty, May 05 '20 at 13:04

score 0 · Accepted Answer · answered May 05 '20 at 13:38

Presumably, up is a value that can take up to three values: 0, 1, and NULL.

The NVL() function is a database-specific function for COALESCE(). They do the same thing.

So, simply look at what the results are:

 up     1 - coalesce(up, 0)
  1               0
  0               1
NULL              1

So, it is "flipping the switch". That is, when "up" is "true", then it switches it to "false". It treats 0 as "false" for this purpose.

1 Answers1