Question

I m running SQL queries on Oracle 10g. I have two tables ( sample data provided below ). i m trying to extract some fields from Table t2 and update the empty columns in table t1. I m encountering the following error:

<>Restrong>ORA 01722: Invalid number ( Pointing to REGEXP_SUBSTR)

I understand this is because of non - numeric data ( like " NO code {...} " ) in my table, that I m trying to extract using the REGEXP_SUBSTR* Expression I was wondering if someone can suggest me some alternative implementations to help me "copy the entire string" instead of throwing an exception.

MERGE
INTO    缩略语
USING   directory_list t2
ON      (REGEXP_SUBSTR(codelist,  [^.]+ , 1) = t2.tcode)
WHEN MATCHED THEN
UPDATE
SET    t1.tcode = t2.tcode,
       t1.des   = t2.des

缩略语

Codelist          | T1.tcode   | T1.des
1111.1.803.12.X.Z 
1000.2.3232.145.M.P        
300.12.2982.45.X.Y         
NO code {...}  
1111.1.803.12.X.Z

directory_list t2
    tcode              |   DES
    1000           | powervalue100
    300                | powermax300
    20                 | powermin20
    NO code {...}      | maxvalue plus
    1000           | powervalue100

感谢

Novice

Answer 1

您可在没有“编码清单”时使用化学文摘社检测。

MERGE
INTO    temptab t1
USING   directory_list t2
ON      (CASE WHEN INSTR(codelist, . ) = 0 THEN codelist
              ELSE REGEXP_SUBSTR(codelist,  [^.]+ , 1)
         END = t2.tcode)
WHEN MATCHED THEN
UPDATE
SET    t1.tcode = t2.tcode,
       t1.des   = t2.des

友情链接