Re: Query returns wrong results

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:b0omd7$ls6$1_at_ctb-nnrp2.saix.net...
> Steven wrote:
>
> > I tried it out but it still does not work. The result I get is .0000
>
> Interesting.
>
> > Get this, if I change the query as follows
> >> SELECT number_column, FROM my_table WHERE number_column > 5
> >
> > I still get rows with 0 value !!!!! not possible. But it's happening.
I
> > dropped all statistics but to no avail.
>
> Reclassified from interesting to downright weird.
>
> My assumption is that Oracle is correct - i.e. it knows that 0 is less
than
> 5 and will not include 0 when you ask for values bigger than 5.
>
> Seeing that this does not happen, something must be wrong or must be
> interfering with the SQL statement. I.e. the statement "col_value > 5"
true
> because it is - even though we think col_value is zero.
>
> For example, the column name you put in the WHERE clause is resolved as
> something different. A mix up with synonyms, views or functions.
>
> Consider this:
> CREATE OR REPLACE sys.dummy RETURN varchar2 AS
> BEGIN
> return( 'fubar' );
> END;
>
> It will cause the following two SQLs to be different due to a simple
> omission that changes how the "column name" is resolved:
>
> SELECT
> sys.dummy
> FROM dual sys
>
> vs. spelling mistake that changes the meaning of "sys.dummy"
>
> SELECT
> sys.dummy
> FROM dual syst
>
> Okay, a silly example. But slippery fingers, synonyms, views and the like,
> can change an innocent looking SQL statement into something that produces
> queuer results.
>
> Well, it is either that, or Oracle does think that zero is bigger than
> five... which will also imply that small circle only has 180 degrees and a
> very large circle, 360 degrees. ;-)
>
> --
> Billy
>

Well I identified the problem.

when using SELECT TO_CHAR(number_column,'9.0000') the result is:

0.0000

however, other columns in other tables for the same datatype give me:

.0000

no preceeding zero before the decimal point. Once I update the row with an UPDATE statement the preceeding zero disappears and all works well. it's seems a problem with the client application entering data which oracle does not like. Once I solve why this is happening I would of fixed the problem.

thanks for your help,
Steve. Received on Thu Jan 23 2003 - 09:42:55 CST