4 Replies Latest reply: Jul 24, 2014 4:40 PM by rp0428 RSS

    ORA-01722 : invalid number in an IN clause

    8f484b6f-bbde-46cb-81bd-88c83e7442a0

      Hi,

       

      I'm having this issue popping on me out of nowhere :

       

      Here is the query that generates the error :

       

      select  *  

      from e_bs_dg_societe

      where dgs_cli_id  in (798,802,674,698,716,799,803,825,804,824,823,805,807,812,813,814,815,816,817,818,820,821,791)  

      and length(dgs_valeur)  =  6  

      and substr(dgs_valeur,1,1)  <>  0 

       

      Now what stumps me is that if I only pass the first five numbers, the query works fine. As in the following :

       

      select  *  

      from e_bs_dg_societe

      where dgs_cli_id  in (798,802,674,698,716 /*,799,803,825,804,824,823,805,807,812,813,814,815,816,817,818,820,821,791*/)  

      and length(dgs_valeur)  =  6  

      and substr(dgs_valeur,1,1)  <>  0 

       

      If I try adding whatever number after 716, then it fails. Repeating any one of the first five in the list works.

       

      If I try a similar query on another table where the column is typed the same (Number), I don't have any problem either.

       

      There is also no issue if I try a direct equals '=' on each number.

        • 1. Re: ORA-01722 : invalid number in an IN clause
          Necronus

          8f484b6f-bbde-46cb-81bd-88c83e7442a0 napisał(-a):

           

          Hi,

           

          I'm having this issue popping on me out of nowhere :

           

          Here is the query that generates the error :

           

          select  *  

          from e_bs_dg_societe

          where dgs_cli_id  in (798,802,674,698,716,799,803,825,804,824,823,805,807,812,813,814,815,816,817,818,820,821,791)  

          and length(dgs_valeur)  =  6  

          and substr(dgs_valeur,1,1)  <>  0 

           

          Now what stumps me is that if I only pass the first five numbers, the query works fine. As in the following :

           

          select  *  

          from e_bs_dg_societe

          where dgs_cli_id  in (798,802,674,698,716 /*,799,803,825,804,824,823,805,807,812,813,814,815,816,817,818,820,821,791*/)  

          and length(dgs_valeur)  =  6  

          and substr(dgs_valeur,1,1)  <>  0 

           

          If I try adding whatever number after 716, then it fails. Repeating any one of the first five in the list works.

           

          If I try a similar query on another table where the column is typed the same (Number), I don't have any problem either.

           

          There is also no issue if I try a direct equals '=' on each number.

           

          I think you have problem with substr  try put

           

           

          select  *  

          from e_bs_dg_societe

          where dgs_cli_id  in (798,802,674,698,716,799,803,825,804,824,823,805,807,812,813,814,815,816,817,818,820,821,791)  

          and length(dgs_valeur)  =  6  

          and substr(dgs_valeur,1,1)  <>  '0'

           

          • 2. Re: ORA-01722 : invalid number in an IN clause
            odie_63

            What's the datatype of DGS_VALEUR column?

             

            This predicate :

            and substr(dgs_valeur,1,1)  <>  0 
            
            

             

            is actually resolved as :

            and to_number(substr(dgs_valeur,1,1)) <>  0
            
            

             

            If DGS_VALEUR is of VARCHAR2 datatype, then that means the content does not represent a number for at least one row targetted by the IN predicate.

             

            If DGS_VALEUR is of NUMBER datatype, then the error may occur in a situation like this :

             

            SQL> with sample_data (num) as (
              2    select 0.123 from dual
              3  )
              4  select *
              5  from sample_data
              6  where substr(num, 1, 1) != 0 ;
            where substr(num, 1, 1) != 0
                  *
            ERROR at line 6:
            ORA-01722: invalid number
            
            

             

            where NUM is first implicitly converted to VARCHAR2 - resulting in ".123" (or ",123" depending on NLS) - then "." after applying SUBSTR, which of course is not a valid number.

            • 3. Re: ORA-01722 : invalid number in an IN clause
              Roger

              Must be a string...just because otherwise the query would be stupid as oracle is not storing leading zeroes....

               

              so i guess the substr(dgs_valeur,1,1) <> 0 is the problem - levae it away at test your query (with the additional entries in the in-list).

               

              hth

              • 4. Re: ORA-01722 : invalid number in an IN clause
                rp0428

                I'm having this issue popping on me out of nowhere :

                Maybe you are and maybe you aren't. You haven't posted ANY such error for us to see.

                Here is the query that generates the error :

                What error? You haven't posted any error.

                 

                If you want help with an 'error' then:

                 

                1. Tell us WHAT you are doing

                2. Show us HOW you are doing it

                3. Show us the actual results you get

                4. Tell us what results you expected to get

                 

                Post a copy of a sql*plus session that shows the query and the error you are talking about.