https://projects.ecoinformatics.org/ecoinfo/https://projects.ecoinformatics.org/ecoinfo/ecoinfo/favicon.ico?14691340362002-11-21T20:14:00ZEcoinformatics RedmineMetacat - Bug #916: bug with 'less-than' query when usng a numberhttps://projects.ecoinformatics.org/ecoinfo/issues/916?journal_id=35642002-11-21T20:14:00ZJing Taotao@nceas.ucsb.edu
<ul></ul><p>The problem turns out that in nodedata fields which has path "southbc", <br />"northbc", "westbc" and "eastbc" there are some records has characters and <br />they caused the problem in casting to number.</p>
<p>According the suggestion from <a class="external" href="http://www.orafaq.com/error/ora-01722.htm">http://www.orafaq.com/error/ora-01722.htm</a>, <br /><abbr title="nodedata">UPPER</abbr> = <abbr title="nodedata">LOWER</abbr> was used to judge if it is a number. But it <br />is not completed. For example, 1*88 is not number but its upper equals lower.</p>
<p>The query was applied to knb database through sqlplus, it worked well. This <br />approach temptary solve the problem because in our database it desn't has <br />record like 1*88.</p>
<p>I took a look in oracle book and didn't find any build-in function to judge a <br />char string is a number. Does anyone have any idea?</p> Metacat - Bug #916: bug with 'less-than' query when usng a numberhttps://projects.ecoinformatics.org/ecoinfo/issues/916?journal_id=35652002-12-02T19:28:35ZMatt Jonesjones@nceas.ucsb.edu
<ul></ul><p>How about using the oracle "TO_NUMBER" function to see if it is a number?</p> Metacat - Bug #916: bug with 'less-than' query when usng a numberhttps://projects.ecoinformatics.org/ecoinfo/issues/916?journal_id=35662002-12-02T20:48:20ZJing Taotao@nceas.ucsb.edu
<ul></ul><p>TO_NUMBER function will return a number data type rather than bealon type. So <br />we couldn't use it in this way: "... AND TO_NUMBER(nodedata) AND ...". I tried <br />to use it in this way: "...TO_NUMBER(nodedata) < 90.0 ..." in the query. But <br />if the nodedata is a string, the query will still throw a sql error: <br />ORA-01722: invalid number</p> Metacat - Bug #916: bug with 'less-than' query when usng a numberhttps://projects.ecoinformatics.org/ecoinfo/issues/916?journal_id=35672003-01-10T17:58:07ZJing Taotao@nceas.ucsb.edu
<ul></ul><p>Changed code was installed in ecoinfo and it works fine.</p> Metacat - Bug #916: bug with 'less-than' query when usng a numberhttps://projects.ecoinformatics.org/ecoinfo/issues/916?journal_id=35682013-03-27T21:15:42ZRedmine Admin
<ul></ul><p>Original Bugzilla ID was 916</p>