4 Replies Latest reply on May 27, 2015 7:50 PM by Oyvind Isene

    oracle error invalid number 01722



      I need help with the following case.



      SELECT mcmskey FROM idmv_entry_simple  WHERE mcentrytype = 'MX_PERSON' AND mcmskeyvalue NOT LIKE '__Q_____'

                                          AND mcmskey NOT IN (SELECT mskey FROM idmv_vallink_basic_active  WHERE mcattrname = 'CCH_PERSON_MISSING_IN_MATRIX')

                                          AND mcmskey IN (select mskey from idmv_vallink_basic where mcattrname = 'MXREF_CCH_COUNTRY_MAPING' and mcsearchvalue IN (

                                                                                          select mskey from idmv_vallink_basic where mcattrname = 'CCH_BR_COUNTRY' and mcsearchvalue = 'IT'))

                                          AND mcmskey = 17963244


      This select brings an error 01722 - invalid number, but if I just replace the obvious number (17963244) with another random one, it works just fine. It doesn't matter if it returns result or not, it works just fine with any other number I've tried, but it doesn't work with this one.


      Also if you remove the last line (AND mcmskey = 17963244) the query always works fine and returns this number (17963244) in the list of results. Also spotted that if I try to filter the RESULT in Oracle SQL Developer tool with this number (17963244) for filter I've received the same error.


      mcmskey and mskey are NUMBER type of columns.


      I've found one explanation that this might be caused, by bad index.


      Any other suggestions or explanations?