Re: How to compare NUMBER's

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 08 Aug 2007 07:08:45 -0700
Message-ID: <1186582125.677920.176280@x35g2000prf.googlegroups.com>

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

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'));

|* 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'));

|* 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. Received on Wed Aug 08 2007 - 09:08:45 CDT