Richard Santos

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….