LoginSignup
1

More than 5 years have passed since last update.

Oracle Model句を利用したデータ移行SQL

Last updated at Posted at 2015-08-20

データ移行背景および使用したSQLについて

前臨床試験における”試験資料の保管情報”を格納するAccessDbより、移行先システムのOracleDbへ”試験資料の保管情報”データを移行するSQL
*SQLを実行するOracleスキーマへはDBA権限が付与されており、AccessDbより出力したExcelファイルを変換したCSV(カンマ区切り)を外部表として参照している(SQL文内の”FILETRANSDATA_QAU_EX”が外部表テーブルに相当する)。
*上記において、Excelを一度出力する理由は、データ分析および区切り文字の選定、セル内の不要な改行コードの除去等を行い、データ移行用として弊害のないCSVを作成するためである。実際には作成したCSVについて、"¥nのみの改行”の除去もしている。

*移行先システムへのデータ移行要件として、下記の3点が挙げられた。
1.”試験資料の保管情報”の項目値”冊数”の分だけレコードを作成して移行先にデータをInsertする。”冊数”が5冊なら、当該の1レコードを5レコードとして移行しなければならない。

2.移行先の主キー
STUDYNO(試験番号)、SEQ(試験番号ごとに一意で付番される連番)、FILENOおよびTOTALNO。ある試験番号について試験資料が5冊の資料と4冊の資料の保管情報が両方ある場合、それぞれTOTALNO=5、4となり、FILENO=1〜5、1〜4の範囲で付番されたレコードが存在する。つまり、 TOTALNO=5、FILENO=2のレコードは5レコードのうちの2レコード目を指す。

3.移行元データの主キー
移行先の主キーのSTUDYNOに該当するのは列"フィールド3"と列"試験番号"を文字連結したもの。しかしながら、当該列の列値に、キー値以外の文字情報が末尾に付加されていたため、変換する必要がある。

*データ移行要件への対応は下記のように行った。これは、1つのSQLで実装した。
1.Oracle SQLのModel句(10g以降で使用可能)により、1レコードを”冊数”分だけ疑似複製する。これにより、移行先データのみ”冊数”分レコードが生成される。
参考URL: 日付範囲を展開したレコードを作成したい

2.移行元の主キー列から不要な情報を取り除くため、”主キー値の文字規則から外れるもの”を、データ上に使用実績のない文字”$”に一度変換し、これを指標にして、不要情報を取り除いたキー値を取得する。変換には正規表現を利用した列値変換関数(REGEXP_REPLACE)を使用する。

SQL本文


insert into FILETRANSDATA (STUDYNO,SEQ,FILENO,UPDATEDATE,UPDATESYAIN_NO,TOTALNO,ADDFLG,QAUFILEFLG,ELECFLG)

select * from
 (
  SELECT  studyno , maxseq + FILENO  as newseq , FILENO ,  sysdate, '888888',NUMTO,0 as ADDFLG,1 as QAUFILEFLG, 0 as ELECFLG
   FROM  
   (  
     select 
      studyno_ex as studyno,
      nvl((select  distinct  to_number(max(seq) over(partition by STUDYNO)) FROM   FILETRANSDATA  where STUDYNO = studyno_ex),0) as maxseq,
      1 as NUMFROM,
      numto_ex as NUMTO 
      FROM  
      (
       select 
          trim(coalesce(substr(studyno_ex,1,instr(studyno_ex,'$')-1),studyno_ex)) as studyno_ex,
          sum(filenum) as numto_ex 
          from 
        (
          select 
            REGEXP_REPLACE(SUBSTRB(TO_SINGLE_BYTE("フィールド3" || "試験番号"),1,20),'[^0-9a-zA-Z ¥ -]','$') as studyno_ex,
            to_number(冊数) as filenum
            from 
            FILETRANSDATA_QAU_EX where trunc(to_date(trim("移管日"))) <= trunc(to_date('2015/04/30'))

         )
           group by 
              trim(coalesce(substr(studyno_ex,1,instr(studyno_ex,'$')-1),studyno_ex))
      )  
    )
      MODEL  PARTITION BY (studyno,maxseq,NUMTO )  
      DIMENSION BY (0 AS IND)  
      MEASURES(NUMFROM as FILENO,NUMTO - NUMFROM as RANGE)  
      RULES(FILENO[for IND from 0 to RANGE[0] increment 1] = FILENO[0] + cv(IND)) 
  ) 
  where
   NUMTO >=1  ;


commit;

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1