Project

General

Profile

Bug #916

bug with 'less-than' query when usng a number

Added by Dan Higgins almost 17 years ago. Updated almost 17 years ago.

Status:
Resolved
Priority:
Normal
Assignee:
Category:
metacat
Target version:
Start date:
11/20/2002
Due date:
% Done:

0%

Estimated time:
Bugzilla-Id:
916

Description

When one uses the 'less-than' operator with a squery action and the comparison
is made to a number (e.g. '<field> less-than 90'), the metacat system reports NO
hits, even though there is data.

The query returned data on Jing's version of Metacat, however.

Problem can be reproduced when a field being compared to a number has string
data that CANNOT be converted to a number. Error keeps metacat from retruning
any hits rather than just skipping node with invalid string data!

History

#1 Updated by Jing Tao almost 17 years ago

The problem turns out that in nodedata fields which has path "southbc",
"northbc", "westbc" and "eastbc" there are some records has characters and
they caused the problem in casting to number.

According the suggestion from http://www.orafaq.com/error/ora-01722.htm,
UPPER = LOWER was used to judge if it is a number. But it
is not completed. For example, 1*88 is not number but its upper equals lower.

The query was applied to knb database through sqlplus, it worked well. This
approach temptary solve the problem because in our database it desn't has
record like 1*88.

I took a look in oracle book and didn't find any build-in function to judge a
char string is a number. Does anyone have any idea?

#2 Updated by Matt Jones almost 17 years ago

How about using the oracle "TO_NUMBER" function to see if it is a number?

#3 Updated by Jing Tao almost 17 years ago

TO_NUMBER function will return a number data type rather than bealon type. So
we couldn't use it in this way: "... AND TO_NUMBER(nodedata) AND ...". I tried
to use it in this way: "...TO_NUMBER(nodedata) < 90.0 ..." in the query. But
if the nodedata is a string, the query will still throw a sql error:
ORA-01722: invalid number

#4 Updated by Jing Tao almost 17 years ago

Changed code was installed in ecoinfo and it works fine.

#5 Updated by Redmine Admin over 6 years ago

Original Bugzilla ID was 916

Also available in: Atom PDF