Bug #916
closedbug with 'less-than' query when usng a number
0%
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!
Updated by Jing Tao over 21 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?
Updated by Matt Jones over 21 years ago
How about using the oracle "TO_NUMBER" function to see if it is a number?
Updated by Jing Tao over 21 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
Updated by Jing Tao over 21 years ago
Changed code was installed in ecoinfo and it works fine.