-2

SQL Analytics: mass query with computations that involve division. Because the data is not clean, you want to prevent it from breaking in case of division by zero.

Ludovic Aubert
  • 9,534
  • 4
  • 16
  • 28
  • What do you want to do with zero divisors? Compute the expression as null, use a different value instead, etc.? – The Impaler Jul 01 '19 at 15:16
  • 2
    Use `NULLIF`? `YourColumn/NULLIF(ZeroValueColumn,0)` – Thom A Jul 01 '19 at 15:18
  • *"Because the data is not clean, you want to prevent it from breaking in case of division by zero."* Sounds like SISO (Shit in shit out) avoidance, best is the clean up the data source? – Raymond Nijland Jul 01 '19 at 15:19
  • Most importantly: if divisor is not zero, i want unchanged beahavior. if divisor is zero, I want to keep query from breaking. – Ludovic Aubert Jul 01 '19 at 15:19
  • also i advice you to read [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Raymond Nijland Jul 01 '19 at 15:20

1 Answers1

1

Instead of 

SELECT a / b;

Use

SELECT a / NULLIF(b,0);

It will return NULL if b=0 instead on breaking on division by zero.

Ludovic Aubert
  • 9,534
  • 4
  • 16
  • 28
  • Not clear why you got the down vote. – John Cappelletti Jul 01 '19 at 15:24
  • My *guess* is that as the OP's question is unclear their answer they gave their own question attracted a downvote from one of the same people, @JohnCappelletti. A total guess mind. I see nothing wrong with the answer itself, but the question certainly needs improving. I could, however, see that this would be a difficult solution to implement if you need to change this in a lot of instances, or if you don't know which expression is causing the divide by zero error. – Thom A Jul 01 '19 at 15:26