Re: How to compare NUMBER's

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 08 Aug 2007 04:25:43 -0700
Message-ID: <1186572343.448393.56180@l70g2000hse.googlegroups.com>

On Aug 8, 9: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.

SQL> select banner from v$version;

BANNER

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production

SQL>
SQL> create table t1 (s1 number(9,3), s2 number(9,3));

Table created.

SQL>
SQL> insert into t1 values(1, 0.534);

1 row created.

SQL> insert into t1 values(1, 1);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> column c2 format 0D999
SQL>
SQL> select s1, (s1-s2)/s2 c2 from t1;

        S1     C2
---------- ------
         1  0.873
         1  0.000

SQL>
SQL> select s1, (s1-s2)/s2 c2 from t1 where (s1-s2)/s2 > 0.5;

S1 C2
---------- ------

1 0.873

So there's nothing wrong with the condition as it seems. What's your Oracle version? What's exact definition of your table, exact data in your table and exact statements you used (I couldn't get 0.872 for the second column with precision of 3?)

Regards,

Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com Received on Wed Aug 08 2007 - 06:25:43 CDT