Bug #916
closed
bug with 'less-than' query when usng a number
Added by Dan Higgins over 21 years ago.
Updated over 21 years ago.
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!
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?
How about using the oracle "TO_NUMBER" function to see if it is a number?
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
Changed code was installed in ecoinfo and it works fine.
Original Bugzilla ID was 916
Also available in: Atom
PDF