Handling Divide By Zero Error In Oracle

Using NULLIF() To Prevent Divide-By-Zero Errors In SQL By Ben Nadel on October 3, 2007 Tags: SQL Boyan Kostadinov just sent me a cool link to an article that is the final oracle nullif part in a four part series that discusses the SQL NULL value. I oracle divide by zero exception haven't read the first three parts yet, but there is a really cool tip in the fourth part on using NULLIF() oracle divide by zero function to prevent divide-by-zero errors in a SQL call.The idea here is that, as with any other form of math that I know of, you cannot divide by zero in a SQL call. Therefore, running

Divisor Is Equal To Zero Oracle

this code:SELECT( 45 / 0 ) AS value;... results in a SQL error being thrown:Error Executing Database Query. [Macromedia] [SQLServer JDBC Driver] [SQLServer] Divide by zero error encountered.To prevent this sort of error from being thrown, author Hugo Kornelis suggests using a NULLIF() in the divisor of the equation. NULLIF() takes two arguments and returns NULL if the vertica nullif two values are the same and can be used to turn the divisor from a zero into a NULL which, in turn, will force the entire equation to become NULL. Therefore, running this code:SELECT( 45 / NULLIF( 0, 0 ) ) AS value;[ #qDivision.value# ]... we get the following output:[ ]Here, the NULLIF( 0, 0 ) returns NULL since zero is equal to zero, which gets the SQL statement to return NULL, which gets ColdFusion to show an empty string. This is a seemingly pointless example since both zero values are hard coded, but imagine if this were a user-entered value, or even better yet, a SQL aggregate or other calculated value (such as might be used in a report or data mining exercise).Now, let's say you want to take this one step further and provide a default value for the equation if NULL is encountered (A default value, though not entirely accurate might make your consuming code more compact as it won't have to deal with exception cases). To set a default value, we could use the ISNULL() or COALESCE() functions: