資料庫-多步操作產生錯誤，請檢查每一步的狀態值

問題描述：在進行資料遷移過程中報的錯，百度也找不到準確答案，哎，自己摸索吧

--按照id排序，匯入成功4045條資料，後面就報錯：多步操作產生錯誤，請檢查每一步的狀態值
[DSRzhizhi]
OrgDBName=zhizhilaoxitong
OrgTable=TC_DUD_PARTY
TarDBName=zhengshi
TarTable=T_XZ_PARTY
IsValid=1
IsTime=
OrgSign=
TarSign=
sGetSQL=select id,caseid,partytypeid,name,personid,sex,birthday,contact,address,diploma,partyrace,driverlicensetypeid,driverlicensedocid,driverlicenseissueoffice,businesslicensetype,businesslicensecode,partyunit,unitaddress,corporation,corperationsex,corperationaddress,corperationcontact,corperationidnumber,updatetime,updateaccountid,state,workunit,workunitaddr,businesslicensetypecontent from TC_DUD_PARTY order by id
sAddSQL=insert into T_XZ_PARTY (PARTY_ID,CASE_ID,party_type,party_name,party_idnum,party_sex,party_birth,party_contno,party_addr,party_edudegree,party_folk,types_driv_license,driver_licenseno,DRIVER_LICENSEISSUE,qc_category,qc_no,company_name,unit_address,legal_name,CORPERATIONSEX,corperationaddress,corperationcontact,corperationidnumber,modify_time,modify_person,ISESCAPE,party_post,unitaddr,businesslicensetypecontent,ZHIDUI,LAOXITONG) Values (:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,2,1)
TbTimeGap=5000
SWhereSQL=
SUpdateSQL=

對欄位型別、長度都進行了對比，沒有什麼問題，難道是日期欄位超出了範圍麼（機智^_^）

於是取4045後面的幾條資料檢查：

select id,
       caseid,
       partytypeid,
       name,
       personid,
       sex,
       birthday,
       contact,
       address,
       diploma,
       partyrace,
       driverlicensetypeid,
       driverlicensedocid,
       driverlicenseissueoffice,
       businesslicensetype,
       businesslicensecode,
       partyunit,
       unitaddress,
       corporation,
       corperationsex,
       corperationaddress,
       corperationcontact,
       corperationidnumber,
       updatetime,
       updateaccountid,
       state,
       workunit,
       workunitaddr,
       businesslicensetypecontent
 from (
select id,
       caseid,
       partytypeid,
       name,
       personid,
       sex,
       birthday,
       contact,
       address,
       diploma,
       partyrace,
       driverlicensetypeid,
       driverlicensedocid,
       driverlicenseissueoffice,
       businesslicensetype,
       businesslicensecode,
       partyunit,
       unitaddress,
       corporation,
       corperationsex,
       corperationaddress,
       corperationcontact,
       corperationidnumber,
       updatetime,
       updateaccountid,
       state,
       workunit,
       workunitaddr,
       businesslicensetypecontent,
       row_number() over(order by id) rn
  from TC_DUD_PARTY
  order by id) where rn between 4044 and 4048

果然發現問題，birthday這個欄位值不對，隨便查詢下就查出了許多錯誤的資料

1900年到現在已經118歲了，應該足夠了，小於1900年的都屬於髒資料，所以查詢的時候要進行過濾

where t.birthday >= to_date('1900/1/1 00:00:00', 'yyyy-MM-dd HH24:mi:ss')

select id,
       caseid,
       partytypeid,
       name,
       personid,
       sex,
       birthday,
       contact,
       address,
       diploma,
       partyrace,
       driverlicensetypeid,
       driverlicensedocid,
       driverlicenseissueoffice,
       businesslicensetype,
       businesslicensecode,
       partyunit,
       unitaddress,
       corporation,
       corperationsex,
       corperationaddress,
       corperationcontact,
       corperationidnumber,
       updatetime,
       updateaccountid,
       state,
       workunit,
       workunitaddr,
       businesslicensetypecontent
 from (
select id,
       caseid,
       partytypeid,
       name,
       personid,
       sex,
       birthday,
       contact,
       address,
       diploma,
       partyrace,
       driverlicensetypeid,
       driverlicensedocid,
       driverlicenseissueoffice,
       businesslicensetype,
       businesslicensecode,
       partyunit,
       unitaddress,
       corporation,
       corperationsex,
       corperationaddress,
       corperationcontact,
       corperationidnumber,
       updatetime,
       updateaccountid,
       state,
       workunit,
       workunitaddr,
       businesslicensetypecontent,
       row_number() over(order by id) rn
  from TC_DUD_PARTY t where t.birthday >= to_date('1900/1/1 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
  order by id) where rn between 4044 and 4048

最後別忘了對遷移指令碼加上過濾條件：

[DSRzhizhi]
OrgDBName=zhizhilaoxitong
OrgTable=TC_DUD_PARTY
TarDBName=zhengshi
TarTable=T_XZ_PARTY
IsValid=1
IsTime=
OrgSign=
TarSign=
sGetSQL=select id,caseid,partytypeid,name,personid,sex,birthday,contact,address,diploma,partyrace,driverlicensetypeid,driverlicensedocid,driverlicenseissueoffice,businesslicensetype,businesslicensecode,partyunit,unitaddress,corporation,corperationsex,corperationaddress,corperationcontact,corperationidnumber,updatetime,updateaccountid,state,workunit,workunitaddr,businesslicensetypecontent from TC_DUD_PARTY where birthday >= to_date('1900/1/1 00:00:00', 'yyyy-MM-dd HH24:mi:ss') order by id
sAddSQL=insert into T_XZ_PARTY (PARTY_ID,CASE_ID,party_type,party_name,party_idnum,party_sex,party_birth,party_contno,party_addr,party_edudegree,party_folk,types_driv_license,driver_licenseno,DRIVER_LICENSEISSUE,qc_category,qc_no,company_name,unit_address,legal_name,CORPERATIONSEX,corperationaddress,corperationcontact,corperationidnumber,modify_time,modify_person,ISESCAPE,party_post,unitaddr,businesslicensetypecontent,ZHIDUI,LAOXITONG) Values (:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,:s,2,1)
TbTimeGap=5000
SWhereSQL=
SUpdateSQL=

測試OK~~