1 Reply Latest reply on Nov 21, 2007 6:50 AM by 602093

    ORA-01722: invalid number for getting PR information

    564006
      This is a query ran on Oracle Purchasing database, I want to select all those purchase requisitions which have accounting segment3 value between 100000 and 199999. The query errors out.

      As in the below query, even though I said "to_number(ccds.segment3) account_seg" in the inner select query but when I give a where condition "account_seg between 150000 and 160000" in the outer query, the sql errors out with "ORA-01722: invalid number" error.

      By commenting out statement "where account_seg between 150000 and 160000", I checked the values returned for segment 3 are all digits and no alphabets.

      Whats can be the issue ?

      ERROR at line 16:
      ORA-01722: invalid number


      select requisition_header_id from
      (
      select
      prls.requisition_header_id requisition_header_id
      ,to_number(ccds.segment3) account_seg
      from
      PO_REQUISITION_headers_all prhs
      ,PO_REQUISITION_LINES_all prls
      ,PO_REQ_DISTRIBUTIONS_ALL prds
      ,gl_code_combinations_kfv ccds
      where
      prhs.requisition_header_id=prls.requisition_header_id
      and prls.requisition_line_id=prds.requisition_line_id
      and prds.code_combination_id=ccds.code_combination_id
      )
      where account_seg between 150000 and 160000
        • 1. Re: ORA-01722: invalid number for getting PR information
          602093
          Hi this error comes because of data problem.

          Some value in segment3 is having alphanumeric data. Check it i am sure about this, i verified!


          Adding more info:
          Some of the alphanumeric data that i found in my database are:

          -
          X00050
          F5000

          Let us replicate your error:

          1. This will run fine:

          SELECT segment3
          FROM gl_code_combinations_kfv
          WHERE segment3 = 'F5000';


          SEGMENT3
          -------------------------
          F5000

          But when we say TO_NUMBER of this value, see what happens:

          SELECT TO_NUMBER(segment3)
          FROM gl_code_combinations_kfv
          WHERE segment3 = 'F5000';

          SELECT TO_NUMBER(segment3) FROM gl_code_combinations_kfv WHERE segment3 = 'F5000'
          *
          ERROR at line 1:
          ORA-01722: invalid number

          *****************************************
          Error proved!
          *****************************************

          Thanks,
          Jithendra

          Error Proof added
          Jithendra