Is it safe to remove when not part in this case statement?

January 5, 2023

The below Case Statement is part of an Select Query.

Is it safe to remove when not part in this case statement ?

Can the when not part removed ?
Will removing the when not , will have any difference ?

WHERE
CASE
WHEN o.order_id <> '0' THEN
  CASE
  WHEN (
      a.acc_id IS NULL
      AND
      o.order_status = ‘a’
    )
    THEN
    1
  WHEN NOT (
      a.acc_id IS NULL
      AND
      o.order_status = ‘a’
    )
    THEN
    0
  END
  ELSE CASE
  
  WHEN (
      (
        o.order_id = '0'
      )
      AND
      a.acc_id IS NULL
      AND
      o.order_status = ‘a’
    )
    THEN
    1
  WHEN NOT (
      (
        o.order_id = '0'
      )
      AND
      a.acc_id IS NULL
      AND
      o.order_status = ‘a’
    )
    THEN
    0
  END
END
= 1

>Solution :

You have a case when returning 1 and a when not (same criteria) to return 0. Case when defaults to null for scenarios that are not covered. So, if you remove the when not part, then the expression will evaluate to null instead of 0 and null=1 will filter out the records having such values. So, it seems that it will not be harmful to remove the when not part, but it will be less readable. I would use else 0 instead of the when not:

WHERE
CASE
WHEN o.order_id <> '0' THEN
  CASE
  WHEN (
      a.acc_id IS NULL
      AND
      o.order_status = ‘a’
    )
    THEN
    1
    ELSE
    0
  END
  ELSE CASE
  
  WHEN (
      (
        o.order_id = '0'
      )
      AND
      a.acc_id IS NULL
      AND
      o.order_status = ‘a’
    )
    THEN
    1
    ELSE
    0
  END
END
= 1