#データ移行背景および使用したSQLについて
前臨床試験における”試験資料の保管情報”を格納するAccessDbより、移行先システムのOracleDbへ”試験資料の保管情報”データを移行するSQL
*SQLを実行するOracleスキーマへはDBA権限が付与されており、AccessDbより出力したExcelファイルを変換したCSV（カンマ区切り）を外部表として参照している（SQL文内の”FILETRANSDATA_QAU_EX”が外部表テーブルに相当する）。
*上記において、Excelを一度出力する理由は、データ分析および区切り文字の選定、セル内の不要な改行コードの除去等を行い、データ移行用として弊害のないCSVを作成するためである。実際には作成したCSVについて、"¥nのみの改行”の除去もしている。
*移行先システムへのデータ移行要件として、下記の3点が挙げられた。
1.”試験資料の保管情報”の項目値”冊数”の分だけレコードを作成して移行先にデータをInsertする。”冊数”が５冊なら、当該の１レコードを５レコードとして移行しなければならない。
2.移行先の主キー
STUDYNO(試験番号)、SEQ(試験番号ごとに一意で付番される連番)、FILENOおよびTOTALNO。ある試験番号について試験資料が５冊の資料と４冊の資料の保管情報が両方ある場合、それぞれTOTALNO=5、４となり、FILENO=1〜５、1〜４の範囲で付番されたレコードが存在する。つまり、 TOTALNO=5、FILENO=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;