Path: text.usenetserver.com!out03a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!x35g2000prf.googlegroups.com!not-for-mail From: Charles Hooper Newsgroups: comp.databases.oracle.misc Subject: Re: How to compare NUMBER's Date: Wed, 08 Aug 2007 07:08:45 -0700 Organization: http://groups.google.com Lines: 146 Message-ID: <1186582125.677920.176280@x35g2000prf.googlegroups.com> References: NNTP-Posting-Host: 65.118.7.2 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1186582129 9361 127.0.0.1 (8 Aug 2007 14:08:49 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 8 Aug 2007 14:08:49 +0000 (UTC) In-Reply-To: User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: x35g2000prf.googlegroups.com; posting-host=65.118.7.2; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.misc:249157 X-Received-Date: Wed, 08 Aug 2007 10:50:50 EDT (text.usenetserver.com) On Aug 8, 1: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. Sybrand makes a good point. In some versions of Oracle, if something looks like a number (or date) in a VARCHAR2 column, it will be automatically treated as a number in a comparison. David likely found the answer for you. I will test his suggestion below on Oracle 10.2.0.2: CREATE TABLE T1 ( S1 VARCHAR2(6), S2 NUMBER(12,4)); Working out the value of S2: SELECT 1/1.872 FROM DUAL; 1/1.872 ========== .534188034 INSERT INTO T1 VALUES ('000001',0.5342); INSERT INTO T1 VALUES ('000002',2); COMMIT; SELECT S1, (S1-S2)/S2 FROM T1 WHERE (S1-S2)/S2>0.5; S1 (S1-S2)/S2 ====== ========== 000001 .871958068 The above worked without a problem. What if S2 is a VARCHAR2 also? DROP TABLE T1; CREATE TABLE T1 ( S1 VARCHAR2(6), S2 VARCHAR2(12)); INSERT INTO T1 VALUES ('000001',0.5342); INSERT INTO T1 VALUES ('000002',2); COMMIT; SELECT /*+ GATHER_PLAN_STATISTICS */ S1, (S1-S2)/S2 FROM T1 WHERE (S1-S2)/S2>0.5; S1 (S1-S2)/S2 ====== ========== 000001 .871958068 SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ |* 1 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 | Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter((TO_NUMBER("S1")-TO_NUMBER("S2"))/TO_NUMBER("S2")>.5) You can see the automatic TO_NUMBER that Oracle set up, as suggested by David. What if it is a problem with the number of 0s in front of the decimal in S2? DELETE FROM T1; INSERT INTO T1 VALUES ('000001','000000.5342'); INSERT INTO T1 VALUES ('000002','000002'); COMMIT; SELECT /*+ GATHER_PLAN_STATISTICS */ S1, (S1-S2)/S2 FROM T1 WHERE (S1-S2)/S2>0.5; S1 (S1-S2)/S2 ====== ========== 000001 .871958068 SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); ------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ |* 1 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 | Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter((TO_NUMBER("S1")-TO_NUMBER("S2"))/TO_NUMBER("S2")>.5) Oracle 10.2.0.2 seems to be acting in a predictable way. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.