[thelist] Avoiding Divide by Zero within SQL...

[rudy]

> rudy is going to fall outta his chair laughing at this,

not really, i mean, if you have a problem where you have to write an sql
query statement that must guard against nulls and at the same time check
for a zero divisor, that's no laughing matter

sorry i didn't jump in on this earlier, i've been offline watching "win ben
stein's money" while eating leftover pot roast and swilling a molson ex --
ah, the joys of being unemployed

anyhow, you have a two-fold problem that the CASE construct by itself isn't
going to cover adequately

   select
       case  bar
          when 0 then 0
             else  foo / bar
        end

so what happens when bar is null?  you get a null answer -- which might be
okay, i dunno, you decide, but if you don't want a null answer, change the
above to

   select
       case  coalesce( bar, 0 )
          when 0 then 0
             else  coalesce( foo / bar, 0 )
        end

the second coalesce guards against foo being null

another thing i'd like to point out is that CASE was only recently adopted
in the "persistent stored module" component of the sql-3 standard, so is
not implemented in all databases

both mysql and microsoft's sql/server support it, i'm not sure about
oracle, and of course in microsoft access you'd probably use nested IIFs
and ISNULLs

anyhow, without the use of the CASE structure, you still have to test for
zero to prevent a divide error

   select 0
      from yourtable
      where bar is null or bar=0
  union all
   select  coalesce ( foo / bar, 0 )
      from yourtable
      where bar is not null and bar<>0

a good optimizer will recognize that the two WHERE clauses are
complementary and mutually exclusive and do both subqueries in a single
pass of the table

since you can use CASE, you might not really need the above, but since you
are adding stuff to your memory banks, remember that there are usually
several ways to achieve a goal in sql


rudy