0

I was reading a query and came upon the following code:

nvl(case when month>start then null else 0 end, 1)

and it seemed to me there was no reason for the nvl.

Why not do this:

case when month>start then 1 else 0 end

?

Bruce
  • 301
  • 2
  • 13
  • 2
    Ask the person who wrote it. If they've any sense, however, they've long since fled the scene of the crime. It's utterly redundant code. – MatBailie Oct 11 '21 at 02:01
  • @MatBailie Thanks - can't upvote yet, apparently I do not have enough reputation points here yet. – Bruce Oct 11 '21 at 02:47

1 Answers1

0

This is an escape clause to get the correct result when [month] has the value NULL.

Comparing NULL > start will yield 0 instead of 1. Using the NVL function will correct the NULL value to 1 as intended.

In case that [month] can never be NULL (column check?) then this code is redundant.

Knut Boehnert
  • 488
  • 5
  • 10
  • `CASE WHEN NULL > start THEN NULL ELSE 0 END` will evaluate to 0, not NULL. The condition does evaluate to NULL, as that isn't TRUE the THEN doesn't activate and the CASE expression moves on, in this code it moves on to the ELSE. Use this fiddle and change the database to any dbms you like, you always get 0... https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=738e169e0bac420fb11ca062cdab3d6d – MatBailie Oct 11 '21 at 09:01
  • Thanks @MatBailie - will correct my answer. – Knut Boehnert Oct 13 '21 at 17:47