User:Kingkong987611/sandbox

select * from (with sourceTB as( ( select 					 UCASE(sd.query_key) as idn_ban,					  sd.main_code, sd.bank_code, 			--		  right(digits(day(sd.date)),2) as day, 				--	  si.inq_purpose_1,					  sd.ep_id as user_id, 					  (to_char(sd.date,'yyyymmdd')-19110000) as query_ymd					  from JCIC.EP_RES_11008 sd						  ,XMLTABLE('$doc/inputs' PASSING XMLPARSE(DOCUMENT CAST('  '||replace(replace(sd.query_input, ' ',''),',','  ')||'   ' as CLOB)) as "doc" COLUMNS query_id VARCHAR(50) PATH 'item/value[1]', inq_purpose_1 VARCHAR(2) PATH 'item/value[2]', inq_purpose_2 VARCHAR(2) PATH 'item/value[3]', inq_purpose_3 VARCHAR(2) PATH 'item/value[4]' ) si					 where 1=1					  and sd.query_type in ('P40', 'P44')					   and si.inq_purpose_1 = 'A'					   and si.inq_purpose_2 = 'A'					   and ( SUBSTR(sd.query_key, 1, 1) Between '0' and '9'  OR							SUBSTR(sd.query_key, 1, 1) Between 'A' and 'z' )					union all					select 					    UCASE(ta.IDN_BAN) as idn_ban, 					    tb.main_code, ta.bank_code,               --right(digits(day(ta.query_date)),2) as day, ta.inq_purpose_1,					    ta.user_id					   , (to_char(ta.query_date, 'yyyymmdd')-19110000) as query_ymd					 from JCIC.ST60011008T ta INNER JOIN JCIC.AB530T TB						ON TA.BANK_CODE=TB.BANK_CODE 					  where 1=1					    and ta.query_item in ('P40', 'P44')					    and ta.inq_purpose_1 = 'A'					    and ta.inq_purpose_2 = 'A'					    AND ( SUBSTR(ta.IDN_BAN, 1, 1) Between '0' and '9'  OR							SUBSTR(ta.IDN_BAN, 1, 1) Between 'A' and 'z')             ) )                   ,matchTb as ( select distinct b.*,kp.start_date from(  ( select UCASE(sd.query_key) as idn_ban, sd.main_code, sd.bank_code, --		 right(digits(day(sd.date)),2) as day, --	 si.inq_purpose_1, sd.ep_id as user_id, (to_char(sd.date,'yyyymmdd')-19110000) as query_ymd from JCIC.EP_RES_11008 sd						 ,XMLTABLE('$doc/inputs'					          PASSING XMLPARSE(DOCUMENT CAST('   '||replace(replace(sd.query_input, ' ',''),',','  ')||'   ' as CLOB)) as "doc"					          COLUMNS					          query_id VARCHAR(50) PATH 'item/value[1]',					          inq_purpose_1 VARCHAR(2) PATH 'item/value[2]',					          inq_purpose_2 VARCHAR(2) PATH 'item/value[3]',					          inq_purpose_3 VARCHAR(2) PATH 'item/value[4]'					      ) si					 where 1=1 and sd.query_type in ('P40', 'P44') and si.inq_purpose_1 = 'A'					  and si.inq_purpose_2 = 'A'					   and ( SUBSTR(sd.query_key, 1, 1) Between '0' and '9'  OR							SUBSTR(sd.query_key, 1, 1) Between 'A' and 'z' ) union all select UCASE(ta.IDN_BAN) as idn_ban, tb.main_code, ta.bank_code, --right(digits(day(ta.query_date)),2) as day, ta.inq_purpose_1, ta.user_id , (to_char(ta.query_date, 'yyyymmdd')-19110000) as query_ymd from JCIC.ST60011008T ta INNER JOIN JCIC.AB530T TB						ON TA.BANK_CODE=TB.BANK_CODE where 1=1 and ta.query_item in ('P40', 'P44') and ta.inq_purpose_1 = 'A'					   and ta.inq_purpose_2 = 'A'					    AND ( SUBSTR(ta.IDN_BAN, 1, 1) Between '0' and '9'  OR							SUBSTR(ta.IDN_BAN, 1, 1) Between 'A' and 'z') )                   				)b,JCIC.KP300T kp       where (   ( (b.main_code = 'T01') and (kp.issue in ('T01', 'E52'))  )    or    ( (b.main_code = 'T02') and (kp.issue in ('T02', 'E53'))  )    or    ( (b.main_code = 'T03') and (kp.issue in ('T03', 'E50'))  )    or    ( (b.main_code = 'T04') and (kp.issue in ('T04', 'E51'))  )    or    ( (not(b.main_code like 'T0%')) and (kp.issue = b.main_code) ) or ( (b.main_code = 'E01') and (kp.issue in ('397')  ))  or ( (b.main_code = 'E02') and (kp.issue in ('395')  ))  or ( (b.main_code = 'E03') and (kp.issue in ('398')  ))  or ( (b.main_code = 'E05') and (kp.issue in ('394')  ))  or ( (b.main_code = 'E06') and (kp.issue in ('396')  ))  or ( (b.main_code = 'E50') and (kp.issue in ('391')  ))  or ( (b.main_code = 'E51') and (kp.issue in ('393')  ))  or ( (b.main_code = 'E52') and (kp.issue in ('390')  ))  or ( (b.main_code = 'E53') and (kp.issue in ('392')  )) ) and b.idn_ban=kp.idn_ban and kp.START_DATE between ( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') - 1 month),'yyyymmdd') - 19110000 ) and ( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') + 3 month),'yyyymmdd') - 19110000 ) ) select sourceTB.*,  ( to_char((to_date((sourceTB.query_ymd + 19110000),'yyyymmdd') - 1 month),'yyyymmdd') - 19110000 ) as b_30,  ( to_char((to_date((sourceTB.query_ymd + 19110000),'yyyymmdd') + 3 month),'yyyymmdd') - 19110000 ) as a_90,  matchTb.START_DATE  from    sourceTB  left join matchTb  on  matchTb.IDN_BAN=sourceTB.IDN_BAN  and  matchTb.bank_CODE=sourceTB.bank_CODE  where matchTb.IDN_BAN is NULL) #產生資料SQL getDataSqlMonth {	#$1:資料年月 #$2:第一層查詢理由
 * 1) 調整現行密合條件邏輯：比對資料檔之「簽約日期」落於(查詢日期-30日)及(查詢日期+90日)之間，即密合成功.

TA_YYYMM=$1 #start_date欄位值為null表示未報送；有值表示雖有報送但不落於(查詢日期-30日)及(查詢日期+90日)之間 echo " select idn_ban, main_code, bank_code, user_id, query_ymd, start_date, b_30, a_90, '$2' from ( 				select UCASE(b.IDN_BAN) as idn_ban, b.MAIN_CODE, b.BANK_CODE, max(b.user_id) as user_id 						,b.query_ymd						,(							case when kp.start_date between								( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') - 1 month),'yyyymmdd') - 19110000 )								and								( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') + 3 month),'yyyymmdd') - 19110000 )							then 'match'							else kp.start_date							end						) as kp300t_mh						,kp.start_date						,( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') - 1 month),'yyyymmdd') - 19110000 ) as b_30						,( to_char((to_date((b.query_ymd + 19110000),'yyyymmdd') + 3 month),'yyyymmdd') - 19110000 ) as a_90				from				(					select 					 UCASE(sd.query_key) as idn_ban,					  sd.main_code, sd.bank_code, 					  right(digits(day(sd.date)),2) as day, si.inq_purpose_1, sd.ep_id as user_id, (to_char(sd.date,'yyyymmdd')-19110000) as query_ymd from $TBCREATOR.EP_RES_$TA_YYYMM sd						 ,XMLTABLE('\$doc/inputs'					          PASSING XMLPARSE(DOCUMENT CAST('   '||replace(replace(sd.query_input, ' ',''),',','  ')||'   ' as CLOB)) as \"doc\"					          COLUMNS					          query_id VARCHAR(50) PATH 'item/value[1]',					          inq_purpose_1 VARCHAR(2) PATH 'item/value[2]',					          inq_purpose_2 VARCHAR(2) PATH 'item/value[3]',					          inq_purpose_3 VARCHAR(2) PATH 'item/value[4]'					      ) si					 where 1=1 and sd.query_type in ('P40', 'P44') and si.inq_purpose_1 = '$2' and si.inq_purpose_2 = 'A'					  and ( SUBSTR(sd.query_key, 1, 1) Between '0' and '9'  OR							SUBSTR(sd.query_key, 1, 1) Between 'A' and 'z' ) union all select UCASE(ta.IDN_BAN) as idn_ban, tb.main_code, ta.bank_code, right(digits(day(ta.query_date)),2) as day, ta.inq_purpose_1, ta.user_id , (to_char(ta.query_date, 'yyyymmdd')-19110000) as query_ymd from $TBCREATOR.ST600${TA_YYYMM}T ta INNER JOIN $TBCREATOR.AB530T TB						ON TA.BANK_CODE=TB.BANK_CODE where 1=1 and ta.query_item in ('P40', 'P44') and ta.inq_purpose_1 = '$2' and ta.inq_purpose_2 = 'A'					   AND ( SUBSTR(ta.IDN_BAN, 1, 1) Between '0' and '9'  OR							SUBSTR(ta.IDN_BAN, 1, 1) Between 'A' and 'z' ) )b left outer join $TBCREATOR.kp300t kp on 					kp.idn_ban = UCASE(b.idn_ban)					and ( case when b.main_code = 'T01' then kp.issue in ('E52','T01') when b.main_code = 'T02' then kp.issue in ('E53','T02') when b.main_code = 'T03' then kp.issue in ('E50','T03') when b.main_code = 'T04' then kp.issue in ('E51','T04') else kp.issue = b.main_code end )				GROUP BY UCASE(b.IDN_BAN), b.MAIN_CODE, b.BANK_CODE, b.user_id, b.query_ymd, to_char(kp.ins_date,'yyyymmdd'),kp.r_name,kp.start_date ) where kp300t_mh is null or kp300t_mh <> 'match'"

}

391	3910001	"E50 T03"	"E500001 T030001" 394	3940001	 E05	    E050001

kp300t ,pa910t

一卡通公司，

查詢單位為T030001(原電票)，查詢日期為7/21，7/27有報送資料，報送之簽約日期為7/11，理論上應該會密合，但是出現在一卡通P91之A類第13筆未密合名單中.

PS： 一卡通公司有E500001跟T030001 二個代號，這筆個案客戶在一卡通的電支跟電票業務皆有往來，皆有報送資料， (1)電支：報送簽約日為3/7    (特店代號：202207XXXXXXXXXX) (2)電票：報送簽約日為7/11  (特店代號：855977XXXX)

會增加條件，修正後， or ( (b.main_code = 'E01') and (kp.issue in ('397') )) or ( (b.main_code = 'E02') and (kp.issue in ('395') )) or ( (b.main_code = 'E03') and (kp.issue in ('398') )) or ( (b.main_code = 'E05') and (kp.issue in ('394') )) or ( (b.main_code = 'E06') and (kp.issue in ('396') )) or ( (b.main_code = 'E50') and (kp.issue in ('391') )) or ( (b.main_code = 'T03') and (kp.issue in ('391') )) or ( (b.main_code = 'E51') and (kp.issue in ('393') )) or ( (b.main_code = 'T04') and (kp.issue in ('393') )) or ( (b.main_code = 'E52') and (kp.issue in ('390') )) or ( (b.main_code = 'T01') and (kp.issue in ('390') )) or ( (b.main_code = 'E53') and (kp.issue in ('392') )) or ( (b.main_code = 'T02') and (kp.issue in ('392') ))