Re: How to compare NUMBER's

On Wed, 8 Aug 2007 13:19:59 +0800, "Jimmy" <lofenee_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.
>
>

Two remarks
/* rant on */ I am aware most posters here expect the few responders do keep track of the versions they are using. Or they expect Oracle never changes, and a problem occurs in every version (This might of course apply to Microsucks Sql server) or they can be bothered to type something like x.x.x.x. What annoys me most is that regular posters have always been informed they *must* post their version, and STILL IGNORE THAT ADVICE. Why the bloody hell do you post this? Log a Service Request, and notice you HAVE to specify a version!!!! /* rant on */

I have the strange feeling the lack of parentheses might be the cause of it, as s2>0.5 is a valid expression with a valid result (either true or false) and Oracle never implemented a proper BNF parser to parse SQL.

-- 
Sybrand Bakker
Senior Oracle DBA