Index: dbs/banksyst/views/lt_ltcr_crdt2_daily.sql =================================================================== --- dbs/banksyst/views/lt_ltcr_crdt2_daily.sql (revision 79776) +++ dbs/banksyst/views/lt_ltcr_crdt2_daily.sql (working copy) @@ -11,7 +11,8 @@ and deal_hist_id in(select max(deal_hist_id) from deal_vam_overdraft_hist_view dh1 where dh1.deal_id = dh.deal_id - and dh1.bank_date <= bd.g_date) + and dh1.bank_date <= bd.g_date + group by dh1.deal_id) order by deal_id) ,first_time_linked_warranty as (select d.warranty_id @@ -25,7 +26,8 @@ from dealwarranty_hist dwh1 where dwh1.warranty_id = dwh.warranty_id and dwh1.deal_id = dwh.deal_id - and dwh1.hist_bank_date <= bd.g_date)) ddd + and dwh1.hist_bank_date <= bd.g_date + group by dwh.deal_id)) ddd where d.warranty_id = ddd.warranty_id and d.hist_action = 'I' and d.hist_bank_date = bd.g_date @@ -52,7 +54,8 @@ or hist_id in(select max(hist_id) from banksyst.warranties_hist wh1 where wh1.warranty_id = wh.warranty_id - and wh1.hist_bank_date < bd.g_date) + and wh1.hist_bank_date < bd.g_date + group by wh1.warranty_id) ) and wh.hist_action <> 'D' and nvl((select count(1) @@ -63,7 +66,8 @@ from banksyst.dealwarranty_hist dwh1 where dwh1.deal_id = dwh.deal_id and dwh1.warranty_id = dwh.warranty_id - and dwh1.hist_bank_date <= bd.g_date) + and dwh1.hist_bank_date <= bd.g_date + group by dwh1.deal_id) and hist_action <> 'D' and dwh.warranty_id = wh.warranty_id group by warranty_id) @@ -81,7 +85,8 @@ ,bsdate bd where dwh2.warranty_id = dwh.warranty_id and dwh2.deal_id = dwh.deal_id - and dwh2.hist_bank_date <= bd.g_date)) + and dwh2.hist_bank_date <= bd.g_date + group by dwh2.deal_id)) and not exists(select 1 from first_time_linked_warranty f where f.warranty_id = wh.warranty_id) @@ -105,7 +110,8 @@ where hist_id in(select max(hist_id) from banksyst.warranties_hist wh1 where wh1.warranty_id = wh.warranty_id - and wh1.hist_bank_date <= bd.g_date) + and wh1.hist_bank_date <= bd.g_date + group by wh1.warranty_id) and exists(select 1 from first_time_linked_warranty f where f.warranty_id = wh.warranty_id) @@ -121,7 +127,8 @@ ,bsdate bd where dwh2.warranty_id = dwh.warranty_id and dwh2.deal_id = dwh.deal_id - and dwh2.hist_bank_date <= bd.g_date)) + and dwh2.hist_bank_date <= bd.g_date + group by dwh.deal_id)) union all select decode(d_col, 1, hist_id, hist_id - 1) hist_id ,hist_action @@ -152,7 +159,8 @@ and hist_id = (select max(hist_id) from banksyst.warranties_hist wh1 where wh1.warranty_id = wh.warranty_id - and wh1.hist_bank_date <= bd.g_date) + and wh1.hist_bank_date <= bd.g_date + group by wh1.warranty_id) and nvl((select count(1) from banksyst.dealwarranty_hist dwh ,bsdate bd @@ -161,7 +169,8 @@ from banksyst.dealwarranty_hist dwh1 where dwh1.deal_id = dwh.deal_id and dwh1.warranty_id = dwh.warranty_id - and dwh1.hist_bank_date <= bd.g_date) + and dwh1.hist_bank_date <= bd.g_date + group by dwh1.deal_id) and hist_action <> 'D' and dwh.warranty_id = wh.warranty_id group by warranty_id) @@ -173,7 +182,8 @@ ,nvl((select max(hist_id) from warranty_hist wh1 where wh1.warranty_id = wh.warranty_id - and wh1.hist_bank_date < bd.g_date), -1) max_hist_id + and wh1.hist_bank_date < bd.g_date + group by wh1.warranty_id), -1) max_hist_id from warranty_hist wh ,bsdate bd where wh.hist_bank_date = bd.g_date @@ -187,7 +197,8 @@ from banksyst.dealwarranty_hist dwh1 where dwh1.warranty_id = dwh.warranty_id and dwh1.deal_id = dwh.deal_id - and dwh1.hist_bank_date < bd.g_date) + and dwh1.hist_bank_date < bd.g_date + group by dwh1.deal_id) ,-1 ) max_hist_id from banksyst.dealwarranty_hist dwh @@ -220,7 +231,8 @@ from dealwarranty_hist dwh1 where dwh1.warranty_id = dwh.warranty_id and dwh1.deal_id = dwh.deal_id - and dwh1.hist_bank_date <= bd.g_date) + and dwh1.hist_bank_date <= bd.g_date + group by dwh1.deal_id) and not exists( select 1 from warranties_hist wh @@ -236,7 +248,8 @@ and wh.hist_id = (select max(wh1.hist_id) from warranties_hist wh1 where wh1.warranty_id = wh.warranty_id - and wh1.hist_bank_date < bd.g_date)) + and wh1.hist_bank_date < bd.g_date + group by wh1.warranty_id)) ,nvl((select max(dh.maturity_date) from deal_vam_overdraft_hist_view dh ,bsdate bd @@ -250,11 +263,14 @@ from banksyst.dealwarranty_hist dwh2 where dwh2.warranty_id = dwh1.warranty_id and dwh2.deal_id = dwh1.deal_id - and dwh2.hist_bank_date < bd.g_date)) + and dwh2.hist_bank_date < bd.g_date + group by dwh2.deal_id)) and dh.deal_hist_id = (select max(deal_hist_id) from deal_vam_overdraft_hist_view dh1 where dh1.deal_id = dh.deal_id - and dh1.bank_date < bd.g_date)) + and dh1.bank_date < bd.g_date + group by dh1.deal_id) + group by dh.deal_id) ,bd.g_date ) ) actual_end_date @@ -267,7 +283,8 @@ from dealwarranty_hist dwh1 where dwh1.warranty_id = dwh.warranty_id and dwh1.deal_id = dwh.deal_id - and dwh1.hist_bank_date < bd.g_date) + and dwh1.hist_bank_date < bd.g_date + group by dwh1.deal_id) and exists(select 1 from today_deal_warranty_link tdwl where tdwl.warranty_id = dwh.warranty_id) @@ -280,7 +297,8 @@ and wh.hist_id = (select max(wh1.hist_id) from warranties_hist wh1 where wh1.warranty_id = wh.warranty_id - and wh1.hist_bank_date <= bd.g_date)) + and wh1.hist_bank_date <= bd.g_date + group by wh1.warranty_id)) ,nvl((select max(dh.maturity_date) from deal_vam_overdraft_hist_view dh ,bsdate bd @@ -294,11 +312,15 @@ from banksyst.dealwarranty_hist dwh2 where dwh2.warranty_id = dwh1.warranty_id and dwh2.deal_id = dwh1.deal_id - and dwh2.hist_bank_date <= bd.g_date)) + and dwh2.hist_bank_date <= bd.g_date + group by dwh2.deal_id)) and dh.deal_hist_id = (select max(deal_hist_id) from deal_vam_overdraft_hist_view dh1 where dh1.deal_id = dh.deal_id - and dh1.bank_date <= bd.g_date)) + and dh1.bank_date <= bd.g_date + group by dh1.deal_id) + group by dh.deal_id + ) ,bd.g_date ) ) actual_end_date @@ -465,7 +487,8 @@ from banksyst.dealwarranty_hist dwh1 where dwh1.deal_id = dwh.deal_id and dwh1.warranty_id = dwh.warranty_id - and dwh1.hist_bank_date <= bd.g_date) + and dwh1.hist_bank_date <= bd.g_date + group by dwh1.deal_id) and dwh.hist_action <> 'D') deals ,banksyst.deal d where wh.warranty_id = wc.warranty_id @@ -550,7 +573,8 @@ from addinfo_history ah1 where ah1.customer_id = ah.customer_id and ah1.type_id = ah.type_id - and ah1.hist_bank_date < bd.g_date) + and ah1.hist_bank_date < bd.g_date + group by ah1.customer_id) ,-1 ) max_hist_id from addinfo_history ah @@ -627,7 +651,8 @@ from banksyst.customer_hist ch ,bsdate bd where ch.customer_id = c.customer_id - and ch.hist_bank_date <= bd.g_date) + and ch.hist_bank_date <= bd.g_date + group by ch.customer_id) and (text_default_status_cnt + text_unlike_cnt) > 2) ,data_block_4 as (select wh.warranty_id @@ -647,7 +672,8 @@ and c.hist_id = (select max(ch.hist_id) from banksyst.customer_hist ch where ch.customer_id = c.customer_id - and ch.hist_bank_date <= bd.g_date) + and ch.hist_bank_date <= bd.g_date + group by ch.customer_id) and exists(select warranty_id, count(1) from warranty_hist wh1 where wh1.warranty_id = wh.warranty_id