Path: text.usenetserver.com!out03a.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!l70g2000hse.googlegroups.com!not-for-mail From: "Vladimir M. Zakharychev" Newsgroups: comp.databases.oracle.misc Subject: Re: How to compare NUMBER's Date: Wed, 08 Aug 2007 04:25:43 -0700 Organization: http://groups.google.com Lines: 78 Message-ID: <1186572343.448393.56180@l70g2000hse.googlegroups.com> References: NNTP-Posting-Host: 213.150.81.158 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1186572343 2390 127.0.0.1 (8 Aug 2007 11:25:43 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 8 Aug 2007 11:25:43 +0000 (UTC) In-Reply-To: User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.6) Gecko/20070725 Firefox/2.0.0.6,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: l70g2000hse.googlegroups.com; posting-host=213.150.81.158; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.misc:249155 X-Received-Date: Wed, 08 Aug 2007 10:45:21 EDT (text.usenetserver.com) On Aug 8, 9:19 am, "Jimmy" 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