Re: Query returns wrong results

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:b0ohot$jqg$1_at_ctb-nnrp2.saix.net...
> Steven wrote:
>
> > I have a table with a number(15,4) column.
> >
> > If I query that table as follows:
> >
> > select * from mytable where number_column > 0
> >
> > If get the rows containing the 0 value !!!!!! I should not. When I
look
> > in sqlplus the number is really 0, the only way it does not display is
> > when I do a select with greater then 1.
> >
> > I don't get it. does anyone know what's going on??
>
> The actual value is likely something like 0.0001 or the display format
uses
> the exponential notation and the front-end you're using has a grid/label
> display width that only displays the zero.
>
> Try:
> SELECT
> TO_CHAR(number_column,'9.0000')
> FROM my_table
> WHERE number_column > 0
>
> You should now see the column value properly displayed. Be careful of how
> the client formats numeric displays. This type of problem can often
happen,
> especially when the client software uses a fixed width grid display (often
> based on the length of the column label and not the length of the actual
> column itself). In the case of SQL*Plus, you need to check its formatting
> settings.
>
>
> --
> Billy

I tried it out but it still does not work. The result I get is .0000

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.

Steve. Received on Thu Jan 23 2003 - 05:50:37 CST