Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!d55g2000hsg.googlegroups.com!not-for-mail
From:  "fitzjarrell@cox.net" <fitzjarrell@cox.net>
Newsgroups: comp.databases.oracle.misc
Subject: Re: How to compare NUMBER's
Date: Wed, 08 Aug 2007 05:54:16 -0700
Organization: http://groups.google.com
Lines: 43
Message-ID: <1186577656.894900.236530@d55g2000hsg.googlegroups.com>
References: <f9bmos$ojk$1@news.cn99.com>
NNTP-Posting-Host: 138.32.32.166
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1186577657 28796 127.0.0.1 (8 Aug 2007 12:54:17 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 8 Aug 2007 12:54:17 +0000 (UTC)
In-Reply-To: <f9bmos$ojk$1@news.cn99.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ;  Embedded Web Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: d55g2000hsg.googlegroups.com; posting-host=138.32.32.166;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.misc:249156
X-Received-Date: Wed, 08 Aug 2007 10:48:31 EDT (text.usenetserver.com)

On Aug 8, 12:19 am, "Jimmy" <lofe...@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