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

CodePudding user response：

Your inner CASE expression:

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

Produces 3 different values 1, 0 and NULL.

• 1 when a.acc_id IS NULL AND o.order_status = 'a'
• 0 when a.acc_id IS NOT NULL OR o.order_status != 'a'
• NULL when o.order_status IS NULL

If you replace the second WHEN clause with an ELSE clause then you will not get the same logic as there will only be 2 values output by the CASE expression.

However, in the outer comparison, neither 0 = 1 or NULL = 1 will match so although you change the logic of the inner CASE expression by removing the WHEN NOT (...) clause you will not change the overall logic of the statement.

You can simplify it all to:

WHERE (   o.order_id <> '0'
      AND a.acc_id IS NULL
      AND o.order_status = 'a'
      )
OR    (   o.order_id = '0' -- There should be an OR o.order_id IS NULL
                           -- filter here but it is eliminated by the
                           -- inner CASE expression in your code.
      AND a.acc_id IS NULL
      AND o.order_status = 'a'
      )

Which simplifies further to:

WHERE o.order_id IS NOT NULL
AND   a.acc_id IS NULL
AND   o.order_status = 'a'

If you want it in a CASE expression then:

WHERE CASE
      WHEN o.order_id IS NOT NULL
      AND  a.acc_id IS NULL
      AND  o.order_status = 'a'
      THEN 1
      END = 1

(but the CASE expression seems pointless.)

CodePudding user response：

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