Re: Order of where clause matters!

From: <wliu2_at_my-deja.com>
Date: 2000/05/26
Message-ID: <8gmidc$k91$1@nnrp1.deja.com>#1/1

In article <jsQodJAWNrL5EwSU_at_rwx777.demon.co.uk>, Steve Haynes <steve_at_rwx777.demon.co.uk> wrote:
> Hi All,
>
> RE: 8.0.5.1.1 on NT4.
>
> I have a strange problem with a divide
> by zero error...
>
> select whatever
> from my_table
> where stuff /* stuff that means "col_a" is never 0 */
> and col_b/col_a > 10;
>
> This gave me a divide by zero error...
> I then changed the order of the where clauses
> so that "and col_b/col_a > 10" was first after the
> "where" keyword and the error went away.
>
> I then added an index and the error came back.
> Drop the index, error goes away.
>

A workaround could be:

select *
from my_table
where stuff /* stuff that means "col_a" is never 0 */ and col_b/decode(col_a, 0, 1, col_a) > 10;

So you won't get the error.

Wei

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri May 26 2000 - 00:00:00 CDT