Re: How to compare NUMBER's

On Aug 8, 12:19 am, "Jimmy" <lofe..._at_yahoo.com.cn> wrote:
> Table t1 has two fields s1,s2 both with type NUMBER(9,3),
>
> After I exectue following sql,
> "SELECT s1, (s1-s2)/s2 FROM t1"
>
> result is:
> 000001, .872
> 000002, 0
>
> While executing "SELECT s1, (s1-s2)/s2 FROM t1 WHERE (s1-s2)/s2>0.5",
>
> result is:
> no rows effected.
>
> Why not "000001, .872"?
>
> I wonder what's wrong if I write the condition in WHERE clause as
> "(s1-s2)/s2>0.5"?
>
> Thanks in advance.

The first problem is that S1 is NOT a NUMBER, it's a VARCHAR2(6), as evidenced by your output; NUMBER values do not preserve leading 0's; VARCHAR2 values do. The second problem arises from the first: the implicit conversion of the VARCHAR2 to a NUMBER, making it, as I call it, 'weakly typed'. You need to explicitly convert these VARCHAR2 values to NUMBER before you execute these calculations:

SELECT s1, (to_number(s1)-to_number(s2))/to_number(s2) FROM t1;

Rewriting your second query:

SELECT s1, (to_number(s1)-to_number(s2))/to_number(s2) FROM t1
WHERE (to_number(s1)-to_number(s2))/to_number(s2)>0.5;

I believe the above queries will 'fix' this self-inflicted problem of yours.

David Fitzjarrell Received on Wed Aug 08 2007 - 07:54:16 CDT