title: ORA-01722 INVALID NUMBER-Unsure of Solution
body: <p>I have been handed this roughly put together a script where we attain more information from other tables based on a long numerical ran number. In past it has worked absolutely fine until recently when we have received an error message &quot;ORA-01722- Invalid Number&quot;</p>
  <p>I have whittled down the ran numbers that are causing this and they are as follows.
  ,'85130081073110917595533'
  ,'85130081074110038783421'
  ,'85130081081111498340562'</p>
  <p>They are no different from other RaN numbers that seem to work such as; '85130060209029787144704'</p>
  <p>The only difference I can identify is that the ones that are pulling the error message all have the same user id within one of the tables, that being user_ID '00000004108005' which is 14 characters, every other merchant_id fluctuates between 8/12. So im wondering if its got something to do with regexp not factoring in the additional zeros?
  To be honest i really don't know and I have done quite a lot of reading into it, I know its directly linked with oracle thinking its dealing with numeric but encounters something else.</p>
  <p>Please find below the syntax and if you have anything to advise to potentially remove this error message it would be massively appreciated if not, at least I posted my second question to you awesome guys :D</p>
  <p>Hope ya'll have  a lovely day, holla if you need anything clarified as im still quite new to the world of stackoverflow/sql</p>
  <pre><code>`
  `WITH TABLE1 as --psuedo table using with clause
                            (
                              SELECT pr.id as seq_nbr,
                              regexp_substr(pr.merchant_reference_code, '[^- ]+',1,1)  as MG_ID,
                              regexp_substr(pr.merchant_reference_code, '[^- ]+',1,2)  as ENTRY_POINT,
                              TABLE2.ID TR_ID,  -- from psuedo table subquery TABLE2
                              TABLE2.op_friendly_order_id, -- from psuedo table subquery TABLE2
                              TABLE2.START_DATE_TIME,  -- from psuedo table subquery TABLE2
                              TABLE2.CU_ID
                              FROM table1 --real table
                              JOIN (-- items in subquery must be reference in above to be usable, and use the subquery alias. in this case TABLE2
                                              SELECT
                                              T.ID,
                                              T.op_friendly_order_id,
                                              NVL2(t.op_friendly_order_id,
                                              T.op_friendly_order_id, T.ID) REF_ID,
                                              T.START_DATE_TIME,
                                              G.CU_ID
                                              FROM table2 T ,
                                             table3 G --real table
                                              WHERE t.cmd_date_created &gt; '01-MAR-2017'
                                              AND T.ID = G.TR_ID
                                   ) TABLE2 --psuedo table using sub-query and joined to real table 
                                                                              on TABLE2.REF_ID = regexp_substr(merchant_reference_code, '[^- ]+',1,3)
                            WHERE request_date &gt;= '01-MAR-2017'
                              )
  SELECT --TABLE1.*
  --TABLE1.SEQ_NBR,
  OT.RRN,
  TABLE1.MG_ID,
  TABLE1.ENTRY_POINT,
  TR_ID,
  TABLE1.OP_FRIENDLY_ORDER_ID,
  TABLE1.START_DATE_TIME BOOKING_DATE_TIME,
  TABLE1.CU_ID
  FROM table 4 ot --real table
  JOIN TABLE1
        ON table1.seq_nbr = ot.rrn
  WHERE ot.btch_date &gt;= '01-MAR-2017'
  AND ot.ran IN (
  '')
  </code></pre>