Calculations with NULL value

Question

I am trying to calculate the difference between two columns, but sometimes one of the columns has NULL value and SQL is ignoring that.

Right now I am using this: if("source indicator" = 'Total OPEX', ("Eur Amount" -"EUR Target") * -1, "Eur Amount" -"EUR Target") as "Diff."

Is there any other way to calculate this (please note that OPEX value) and get the expected results?

Thanks

Which db engine are you using?

NP3
– NP3

2017-05-19 10:38:50 +00:00
Commented May 19, 2017 at 10:38 — NP3
– NP3, Commented May 19, 2017 at 10:38
if is non standard...

JohnHC
– JohnHC

2017-05-19 10:39:06 +00:00
Commented May 19, 2017 at 10:39 — JohnHC
– JohnHC, Commented May 19, 2017 at 10:39

Gordon Linoff · Accepted Answer · 2017-05-19 10:39:14Z

4

You can use case and coalesce():

(case when "source indicator" = 'Total OPEX'
      then coalesce("Eur Amount", 0) - coalesce("EUR Target", 0) * -1
      else coalesce("Eur Amount", 0) - coalesce("EUR Target", 0)
 end)

The advantage of case is that it is ANSI-standard SQL, supported by pretty much all databases. if() is database-dependent.

answered May 19, 2017 at 10:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calculations with NULL value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related