I had a query once that contains a divide operation….
SELECT x, y, ROUND(x/y) as quotient
FROM dataTbl
It always returns a correct value…
but divide by zero error occurs whenever
there is a zero(0) not NULL value in y column…
now, what i want is instead of having
an error, i want it to return a zero(0) value also…
so I wonder if there is a function isZero(), I can simply
change the y value from 0 to NULL to avoid such error…
I was thinking of a code like this: isZero(y, NULL)…
unfortunately, there is no such functions…
I googled for the correct syntax and I came up with
the: NULLIF(fieldName, condition) function…
SELECT x, y, ROUND(x/NULLIF(y, 0)) as quotient
FROM dataTbl
by using the query above, divide by zero error is impossible…
what happen is SQL returns a NULL in y column if its value is zero(0)…
though the error was avoided… the result still does not satisfy me…
it only returns a NULL value in quotient field instead of zero(0)…
well, the solution is simple:
SELECT x, y, ISNULL(ROUND(x/NULLIF(y, 0)), 0) as quotient
FROM dataTbl
by using the ISNULL(fieldName, replacementValue) function,
It sort of like reversed the effect of NULLIF function…
what happen in this function is, if the value IS NULL
then replace it with what ever the replacementValue is…
in my query, it replaces the NULL value by zero(0)…
now that’s satisfying…. divide by zero error was avoided
and quotient field is never NULL….
sdn blogger
sap blogger
This is an excellent post!Thank you, i was a bit of all the divide by zeros errors i was coming across.
Thank you!
This is one of the best thing I found on the internet. Thank you so much..a long awaited project has started moving again…Thank you so much!!! This was an excellent resource!This is an excellent and quick help to keep running my project witout any interruption. thanks alotThis post was excellent and marvellous.Great! thanks.Excellent helped me solve a production problem very quickly indeed!
Fantastic post, clear and concise and solved my problem instantly. Thank you very much, I truly appreciate it!Great work man…Reduced my work by a hell amount time…thanks a lotWorked like a charm. I am using a datagrid to display my data, so I only used the NULLIF function. My datagrid column is set to display zero for Null value. Thanks once againI was about to write my own function to do exactly the same thing as NULLIF until I found this post and realized that one already existed. Thanks!!keep the good work !!!!Doh! Why does it work for everyone else, but I get a “ROUND requires 2 arguments” error?double check the arguments on your ROUND function. ROUND(numeric_expression, length). Length is the precision to which the numeric_expression is to be rounded.Superb , Hiperb , Perfect , Wonderful O La La :)) Thanks …You just saved me, BIG TIME!Mucho gracias!
Would this be the same thing? (db2)SELECT X,Y, CASE WHEN Y = 0 THEN 0 ELSE ROUND(X / Y) END AS quotient
FROM dataTbl
@DaveISNULL(ROUND(x/(NULLIF(y,0)),0),0)
I’m pretty sure there was an error with the posted code unless they are using something different than sql server where it would work. But with SQL Server the code shown does not work. All three of the functions require more than 1 parameter, so the above code should work fine.
Thanks, this works!Thanks a lot….simplified my problems …..on the spot…..Just Excellent