Re: Query returns wrong results

Steven wrote:

> "Steven" <askme_at_nowhere.com> wrote in message
> news:b0ocb5$rllng$1_at_ID-82797.news.dfncis.de...
> > Hi,
> >
> > 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??
> >
> > I'm using oracle 8.1.7.0 on solaris 7
> >
> > Thanks in advance,
> > Steve.
> >
> >
> > --
> > -----------------------------------------------------------------
> > Holidays in Berchtesgaden, Germany:
> > http://www.sonnenkoepfl.de
> > http://unterkunft-berchtesgaden.de
> > http://pension-berchtesgaden.de
> >
> >
>
> I have discovered that if I use the to_number() function then I get the
> correct results. I believe the data stored in the field is somewhat
> strange. For example, if I update the row and enter 0 then the results are
> correct. It seems that the orginal entered value (from a java application)
> is somewhat messed up. even though it display 0 the select statement does
> not believe this is a numeric value.
>
> Anyone know how I can get the datatype of a value for a field??
>
> S.

Nothing strange is going on here. You need to DESC the table as in:

SQL> desc <table_name> to see what the data types are.

If numbers are stored in a CHAR or VARCHAR2 then your choices are either TO_NUMBER or
ALTER TABLE
MODIFY (<column_name> NUMBER(15,4));
as it is obvious that your original statement that it was a NUMBER(15,4) was incorrect.

Oracle is doing exactly what it is supposed to do.

In the future try this to see what is actually stored: SELECT DUMP(column_name)
FROM table_name;

Daniel Morgan Received on Tue Jan 21 2003 - 07:42:18 CST