Re: Query returns wrong results

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

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