vb程序是别人写的，采用了过多的视图，并且互相嵌套，导致效率低下，测试机1万条数据没问题，生产机20万条数据运行不起来，如何改造。1/
create view EsaTransSum_1 as
select rtrim(c.card_name) + '(' + s.mer_on_card + ')' as card_name,
s.cardno,m.mcht_name,
s.iss_mcht_id,s.acq_mcht_id,
s.acq_term_id,
s.agent_mcht_id,
sum(s.pnt_trans_num_successful) as pnt_trans_num_successful,
sum(s.pnt_trans_amt_successful) as pnt_trans_amt_successful,
sum(s.pnt_trans_acq_fee_successful) as pnt_trans_acq_fee_successful,
sum(s.pnt_trans_iss_fee_successful) as pnt_trans_iss_fee_successful,
sum(s.ncr_trans_num_successful) as ncr_trans_num_successful,
sum(s.ncr_trans_amt_successful) as ncr_trans_amt_successful,
sum(s.ncr_trans_acq_fee_successful) as ncr_trans_acq_fee_successful,
sum(s.ncr_trans_iss_fee_successful) as ncr_trans_iss_fee_successful,
sum(s.per_trans_num_successful) as per_trans_num_successful,
sum(s.per_trans_amt_successful) as per_trans_amt_successful,
sum(s.per_trans_acq_fee_successful) as per_trans_acq_fee_successful,
sum(s.per_trans_iss_fee_successful) as per_trans_iss_fee_successful,
sum(s.psr_trans_num_successful) as psr_trans_num_successful,
sum(s.psr_trans_amt_successful) as psr_trans_amt_successful,
sum(s.psr_trans_acq_fee_successful) as psr_trans_acq_fee_successful,
sum(s.psr_trans_iss_fee_successful) as psr_trans_iss_fee_successful,
sum(s.rsa_trans_num_successful) as rsa_trans_num_successful,
sum(s.rsa_trans_amt_successful) as rsa_trans_amt_successful,
sum(s.rsa_trans_acq_fee_successful) as rsa_trans_acq_fee_successful,
sum(s.rsa_trans_iss_fee_successful) as rsa_trans_iss_fee_successful,
sum(s.tsi_trans_num_successful) as tsi_trans_num_successful,
sum(s.tsi_trans_amt_successful) as tsi_trans_amt_successful,
sum(s.tsi_trans_acq_fee_successful) as tsi_trans_acq_fee_successful,
sum(s.tsi_trans_iss_fee_successful) as tsi_trans_iss_fee_successful,
sum(s.tso_trans_num_successful) as tso_trans_num_successful,
sum(s.tso_trans_amt_successful) as tso_trans_amt_successful,
sum(s.tso_trans_acq_fee_successful) as tso_trans_acq_fee_successful,
sum(s.tso_trans_iss_fee_successful) as tso_trans_iss_fee_successful,
sum(s.psn_trans_num_successful) as psn_trans_num_successful,
sum(s.psn_trans_amt_successful) as psn_trans_amt_successful,
sum(s.psn_trans_acq_fee_successful) as psn_trans_acq_fee_successful,
sum(s.psn_trans_iss_fee_successful) as psn_trans_iss_fee_successful
from l2_sum_transactiontwo s,
l2_param_card_name c,
l2_mcht m
where m.mcht_id=s.iss_mcht_id
and substring(m.mcht_role_type,2,1) = '1'
and s.mer_on_card = c.mer_on_card
and m.mer_on_card=c.mer_on_card
and substring(sett_date,1,6) = '201302'
group by s.agent_mcht_id,s.mer_on_card,c.card_name,s.iss_mcht_id,s.cardno,m.mcht_name,s.acq_mcht_id,s.acq_term_id
2/
create view EsaTransSum_2 as
select rtrim(c.card_name) + '(' + s.mer_on_card + ')' as card_name,
s.cardno,
m.mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
s.acq_term_id,
s.agent_mcht_id,
sum(s.pnt_trans_num_successful) as pnt_trans_num_successful,
sum(s.pnt_trans_amt_successful) as pnt_trans_amt_successful,
sum(s.pnt_trans_acq_fee_successful) as pnt_trans_acq_fee_successful,
sum(s.pnt_trans_iss_fee_successful) as pnt_trans_iss_fee_successful,
sum(s.ncr_trans_num_successful) as ncr_trans_num_successful,
sum(s.ncr_trans_amt_successful) as ncr_trans_amt_successful,
sum(s.ncr_trans_acq_fee_successful) as ncr_trans_acq_fee_successful,
sum(s.ncr_trans_iss_fee_successful) as ncr_trans_iss_fee_successful,
sum(s.per_trans_num_successful) as per_trans_num_successful,
sum(s.per_trans_amt_successful) as per_trans_amt_successful,
sum(s.per_trans_acq_fee_successful) as per_trans_acq_fee_successful,
sum(s.per_trans_iss_fee_successful) as per_trans_iss_fee_successful,
sum(s.psr_trans_num_successful) as psr_trans_num_successful,
sum(s.psr_trans_amt_successful) as psr_trans_amt_successful,
sum(s.psr_trans_acq_fee_successful) as psr_trans_acq_fee_successful,
sum(s.psr_trans_iss_fee_successful) as psr_trans_iss_fee_successful,
sum(s.rsa_trans_num_successful) as rsa_trans_num_successful,
sum(s.rsa_trans_amt_successful) as rsa_trans_amt_successful,
sum(s.rsa_trans_acq_fee_successful) as rsa_trans_acq_fee_successful,
sum(s.rsa_trans_iss_fee_successful) as rsa_trans_iss_fee_successful,
sum(s.tsi_trans_num_successful) as tsi_trans_num_successful,
sum(s.tsi_trans_amt_successful) as tsi_trans_amt_successful,
sum(s.tsi_trans_acq_fee_successful) as tsi_trans_acq_fee_successful,
sum(s.tsi_trans_iss_fee_successful) as tsi_trans_iss_fee_successful,
sum(s.tso_trans_num_successful) as tso_trans_num_successful,
sum(s.tso_trans_amt_successful) as tso_trans_amt_successful,
sum(s.tso_trans_acq_fee_successful) as tso_trans_acq_fee_successful,
sum(s.tso_trans_iss_fee_successful) as tso_trans_iss_fee_successful,
sum(s.psn_trans_num_successful) as psn_trans_num_successful,
sum(s.psn_trans_amt_successful) as psn_trans_amt_successful,
sum(s.psn_trans_acq_fee_successful) as psn_trans_acq_fee_successful,
sum(s.psn_trans_iss_fee_successful) as psn_trans_iss_fee_successful
from l2_sum_transactiontwo s,
l2_param_card_name c,
l2_mcht_three m
where m.mcht_id=s.iss_mcht_id
and substring(m.mcht_role_type,2,1) = '1'
and s.mer_on_card = c.mer_on_card
and m.mer_on_card=c.mer_on_card
and substring(sett_date,1,6) = '201302'
and ((left(s.cardno,4)) in(select m.top_mcht_id from l2_mcht_three m where m.sett_flag =''))
and s.iss_mcht_id<>''
and s.agent_mcht_id<>''
group by s.agent_mcht_id,s.mer_on_card,c.card_name,s.iss_mcht_id,s.cardno,m.mcht_name,s.acq_mcht_id,s.acq_term_id
3/
create view EsaTransSum_3
as
select rtrim(c.card_name) + '(' + s.mer_on_card + ')' as card_name,
s.cardno,
m.mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
s.acq_term_id,
s.agent_mcht_id,
sum(s.pnt_trans_num_successful) as pnt_trans_num_successful,
sum(s.pnt_trans_amt_successful) as pnt_trans_amt_successful,
sum(s.pnt_trans_acq_fee_successful) as pnt_trans_acq_fee_successful,
sum(s.pnt_trans_iss_fee_successful) as pnt_trans_iss_fee_successful,
sum(s.ncr_trans_num_successful) as ncr_trans_num_successful,
sum(s.ncr_trans_amt_successful) as ncr_trans_amt_successful,
sum(s.ncr_trans_acq_fee_successful) as ncr_trans_acq_fee_successful,
sum(s.ncr_trans_iss_fee_successful) as ncr_trans_iss_fee_successful,
sum(s.per_trans_num_successful) as per_trans_num_successful,
sum(s.per_trans_amt_successful) as per_trans_amt_successful,
sum(s.per_trans_acq_fee_successful) as per_trans_acq_fee_successful,
sum(s.per_trans_iss_fee_successful) as per_trans_iss_fee_successful,
sum(s.psr_trans_num_successful) as psr_trans_num_successful,
sum(s.psr_trans_amt_successful) as psr_trans_amt_successful,
sum(s.psr_trans_acq_fee_successful) as psr_trans_acq_fee_successful,
sum(s.psr_trans_iss_fee_successful) as psr_trans_iss_fee_successful,
sum(s.rsa_trans_num_successful) as rsa_trans_num_successful,
sum(s.rsa_trans_amt_successful) as rsa_trans_amt_successful,
sum(s.rsa_trans_acq_fee_successful) as rsa_trans_acq_fee_successful,
sum(s.rsa_trans_iss_fee_successful) as rsa_trans_iss_fee_successful,
sum(s.tsi_trans_num_successful) as tsi_trans_num_successful,
sum(s.tsi_trans_amt_successful) as tsi_trans_amt_successful,
sum(s.tsi_trans_acq_fee_successful) as tsi_trans_acq_fee_successful,
sum(s.tsi_trans_iss_fee_successful) as tsi_trans_iss_fee_successful,
sum(s.tso_trans_num_successful) as tso_trans_num_successful,
sum(s.tso_trans_amt_successful) as tso_trans_amt_successful,
sum(s.tso_trans_acq_fee_successful) as tso_trans_acq_fee_successful,
sum(s.tso_trans_iss_fee_successful) as tso_trans_iss_fee_successful,
sum(s.psn_trans_num_successful) as psn_trans_num_successful,
sum(s.psn_trans_amt_successful) as psn_trans_amt_successful,
sum(s.psn_trans_acq_fee_successful) as psn_trans_acq_fee_successful,
sum(s.psn_trans_iss_fee_successful) as psn_trans_iss_fee_successful
from l2_sum_transactiontwo s,
l2_param_card_name c,
l2_mcht_three m
where m.mcht_id=s.iss_mcht_id
and substring(m.mcht_role_type,2,1) = '1'
and s.mer_on_card = c.mer_on_card
and m.mer_on_card=c.mer_on_card
and substring(sett_date,1,6) = '201302'
and ((left(s.cardno,4)) not in(select m.top_mcht_id from l2_mcht_three m where m.top_mcht_id=(left(s.cardno,4))))
and s.iss_mcht_id<>''
and s.agent_mcht_id<>''
and m.top_mcht_id='0000'
group by s.agent_mcht_id,s.mer_on_card,c.card_name,s.iss_mcht_id,s.cardno,m.mcht_name,s.acq_mcht_id,s.acq_term_id
4/
create view EsaTransSum_4 as select * from EsaTransSum_1 union all select * from EsaTransSum_2
5/
create view EsaTransSum_5 as select * from EsaTransSum_4 union all select * from EsaTransSum_3
create view EsaTransSum_1 as
select rtrim(c.card_name) + '(' + s.mer_on_card + ')' as card_name,
s.cardno,m.mcht_name,
s.iss_mcht_id,s.acq_mcht_id,
s.acq_term_id,
s.agent_mcht_id,
sum(s.pnt_trans_num_successful) as pnt_trans_num_successful,
sum(s.pnt_trans_amt_successful) as pnt_trans_amt_successful,
sum(s.pnt_trans_acq_fee_successful) as pnt_trans_acq_fee_successful,
sum(s.pnt_trans_iss_fee_successful) as pnt_trans_iss_fee_successful,
sum(s.ncr_trans_num_successful) as ncr_trans_num_successful,
sum(s.ncr_trans_amt_successful) as ncr_trans_amt_successful,
sum(s.ncr_trans_acq_fee_successful) as ncr_trans_acq_fee_successful,
sum(s.ncr_trans_iss_fee_successful) as ncr_trans_iss_fee_successful,
sum(s.per_trans_num_successful) as per_trans_num_successful,
sum(s.per_trans_amt_successful) as per_trans_amt_successful,
sum(s.per_trans_acq_fee_successful) as per_trans_acq_fee_successful,
sum(s.per_trans_iss_fee_successful) as per_trans_iss_fee_successful,
sum(s.psr_trans_num_successful) as psr_trans_num_successful,
sum(s.psr_trans_amt_successful) as psr_trans_amt_successful,
sum(s.psr_trans_acq_fee_successful) as psr_trans_acq_fee_successful,
sum(s.psr_trans_iss_fee_successful) as psr_trans_iss_fee_successful,
sum(s.rsa_trans_num_successful) as rsa_trans_num_successful,
sum(s.rsa_trans_amt_successful) as rsa_trans_amt_successful,
sum(s.rsa_trans_acq_fee_successful) as rsa_trans_acq_fee_successful,
sum(s.rsa_trans_iss_fee_successful) as rsa_trans_iss_fee_successful,
sum(s.tsi_trans_num_successful) as tsi_trans_num_successful,
sum(s.tsi_trans_amt_successful) as tsi_trans_amt_successful,
sum(s.tsi_trans_acq_fee_successful) as tsi_trans_acq_fee_successful,
sum(s.tsi_trans_iss_fee_successful) as tsi_trans_iss_fee_successful,
sum(s.tso_trans_num_successful) as tso_trans_num_successful,
sum(s.tso_trans_amt_successful) as tso_trans_amt_successful,
sum(s.tso_trans_acq_fee_successful) as tso_trans_acq_fee_successful,
sum(s.tso_trans_iss_fee_successful) as tso_trans_iss_fee_successful,
sum(s.psn_trans_num_successful) as psn_trans_num_successful,
sum(s.psn_trans_amt_successful) as psn_trans_amt_successful,
sum(s.psn_trans_acq_fee_successful) as psn_trans_acq_fee_successful,
sum(s.psn_trans_iss_fee_successful) as psn_trans_iss_fee_successful
from l2_sum_transactiontwo s,
l2_param_card_name c,
l2_mcht m
where m.mcht_id=s.iss_mcht_id
and substring(m.mcht_role_type,2,1) = '1'
and s.mer_on_card = c.mer_on_card
and m.mer_on_card=c.mer_on_card
and substring(sett_date,1,6) = '201302'
group by s.agent_mcht_id,s.mer_on_card,c.card_name,s.iss_mcht_id,s.cardno,m.mcht_name,s.acq_mcht_id,s.acq_term_id
2/
create view EsaTransSum_2 as
select rtrim(c.card_name) + '(' + s.mer_on_card + ')' as card_name,
s.cardno,
m.mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
s.acq_term_id,
s.agent_mcht_id,
sum(s.pnt_trans_num_successful) as pnt_trans_num_successful,
sum(s.pnt_trans_amt_successful) as pnt_trans_amt_successful,
sum(s.pnt_trans_acq_fee_successful) as pnt_trans_acq_fee_successful,
sum(s.pnt_trans_iss_fee_successful) as pnt_trans_iss_fee_successful,
sum(s.ncr_trans_num_successful) as ncr_trans_num_successful,
sum(s.ncr_trans_amt_successful) as ncr_trans_amt_successful,
sum(s.ncr_trans_acq_fee_successful) as ncr_trans_acq_fee_successful,
sum(s.ncr_trans_iss_fee_successful) as ncr_trans_iss_fee_successful,
sum(s.per_trans_num_successful) as per_trans_num_successful,
sum(s.per_trans_amt_successful) as per_trans_amt_successful,
sum(s.per_trans_acq_fee_successful) as per_trans_acq_fee_successful,
sum(s.per_trans_iss_fee_successful) as per_trans_iss_fee_successful,
sum(s.psr_trans_num_successful) as psr_trans_num_successful,
sum(s.psr_trans_amt_successful) as psr_trans_amt_successful,
sum(s.psr_trans_acq_fee_successful) as psr_trans_acq_fee_successful,
sum(s.psr_trans_iss_fee_successful) as psr_trans_iss_fee_successful,
sum(s.rsa_trans_num_successful) as rsa_trans_num_successful,
sum(s.rsa_trans_amt_successful) as rsa_trans_amt_successful,
sum(s.rsa_trans_acq_fee_successful) as rsa_trans_acq_fee_successful,
sum(s.rsa_trans_iss_fee_successful) as rsa_trans_iss_fee_successful,
sum(s.tsi_trans_num_successful) as tsi_trans_num_successful,
sum(s.tsi_trans_amt_successful) as tsi_trans_amt_successful,
sum(s.tsi_trans_acq_fee_successful) as tsi_trans_acq_fee_successful,
sum(s.tsi_trans_iss_fee_successful) as tsi_trans_iss_fee_successful,
sum(s.tso_trans_num_successful) as tso_trans_num_successful,
sum(s.tso_trans_amt_successful) as tso_trans_amt_successful,
sum(s.tso_trans_acq_fee_successful) as tso_trans_acq_fee_successful,
sum(s.tso_trans_iss_fee_successful) as tso_trans_iss_fee_successful,
sum(s.psn_trans_num_successful) as psn_trans_num_successful,
sum(s.psn_trans_amt_successful) as psn_trans_amt_successful,
sum(s.psn_trans_acq_fee_successful) as psn_trans_acq_fee_successful,
sum(s.psn_trans_iss_fee_successful) as psn_trans_iss_fee_successful
from l2_sum_transactiontwo s,
l2_param_card_name c,
l2_mcht_three m
where m.mcht_id=s.iss_mcht_id
and substring(m.mcht_role_type,2,1) = '1'
and s.mer_on_card = c.mer_on_card
and m.mer_on_card=c.mer_on_card
and substring(sett_date,1,6) = '201302'
and ((left(s.cardno,4)) in(select m.top_mcht_id from l2_mcht_three m where m.sett_flag =''))
and s.iss_mcht_id<>''
and s.agent_mcht_id<>''
group by s.agent_mcht_id,s.mer_on_card,c.card_name,s.iss_mcht_id,s.cardno,m.mcht_name,s.acq_mcht_id,s.acq_term_id
3/
create view EsaTransSum_3
as
select rtrim(c.card_name) + '(' + s.mer_on_card + ')' as card_name,
s.cardno,
m.mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
s.acq_term_id,
s.agent_mcht_id,
sum(s.pnt_trans_num_successful) as pnt_trans_num_successful,
sum(s.pnt_trans_amt_successful) as pnt_trans_amt_successful,
sum(s.pnt_trans_acq_fee_successful) as pnt_trans_acq_fee_successful,
sum(s.pnt_trans_iss_fee_successful) as pnt_trans_iss_fee_successful,
sum(s.ncr_trans_num_successful) as ncr_trans_num_successful,
sum(s.ncr_trans_amt_successful) as ncr_trans_amt_successful,
sum(s.ncr_trans_acq_fee_successful) as ncr_trans_acq_fee_successful,
sum(s.ncr_trans_iss_fee_successful) as ncr_trans_iss_fee_successful,
sum(s.per_trans_num_successful) as per_trans_num_successful,
sum(s.per_trans_amt_successful) as per_trans_amt_successful,
sum(s.per_trans_acq_fee_successful) as per_trans_acq_fee_successful,
sum(s.per_trans_iss_fee_successful) as per_trans_iss_fee_successful,
sum(s.psr_trans_num_successful) as psr_trans_num_successful,
sum(s.psr_trans_amt_successful) as psr_trans_amt_successful,
sum(s.psr_trans_acq_fee_successful) as psr_trans_acq_fee_successful,
sum(s.psr_trans_iss_fee_successful) as psr_trans_iss_fee_successful,
sum(s.rsa_trans_num_successful) as rsa_trans_num_successful,
sum(s.rsa_trans_amt_successful) as rsa_trans_amt_successful,
sum(s.rsa_trans_acq_fee_successful) as rsa_trans_acq_fee_successful,
sum(s.rsa_trans_iss_fee_successful) as rsa_trans_iss_fee_successful,
sum(s.tsi_trans_num_successful) as tsi_trans_num_successful,
sum(s.tsi_trans_amt_successful) as tsi_trans_amt_successful,
sum(s.tsi_trans_acq_fee_successful) as tsi_trans_acq_fee_successful,
sum(s.tsi_trans_iss_fee_successful) as tsi_trans_iss_fee_successful,
sum(s.tso_trans_num_successful) as tso_trans_num_successful,
sum(s.tso_trans_amt_successful) as tso_trans_amt_successful,
sum(s.tso_trans_acq_fee_successful) as tso_trans_acq_fee_successful,
sum(s.tso_trans_iss_fee_successful) as tso_trans_iss_fee_successful,
sum(s.psn_trans_num_successful) as psn_trans_num_successful,
sum(s.psn_trans_amt_successful) as psn_trans_amt_successful,
sum(s.psn_trans_acq_fee_successful) as psn_trans_acq_fee_successful,
sum(s.psn_trans_iss_fee_successful) as psn_trans_iss_fee_successful
from l2_sum_transactiontwo s,
l2_param_card_name c,
l2_mcht_three m
where m.mcht_id=s.iss_mcht_id
and substring(m.mcht_role_type,2,1) = '1'
and s.mer_on_card = c.mer_on_card
and m.mer_on_card=c.mer_on_card
and substring(sett_date,1,6) = '201302'
and ((left(s.cardno,4)) not in(select m.top_mcht_id from l2_mcht_three m where m.top_mcht_id=(left(s.cardno,4))))
and s.iss_mcht_id<>''
and s.agent_mcht_id<>''
and m.top_mcht_id='0000'
group by s.agent_mcht_id,s.mer_on_card,c.card_name,s.iss_mcht_id,s.cardno,m.mcht_name,s.acq_mcht_id,s.acq_term_id
4/
create view EsaTransSum_4 as select * from EsaTransSum_1 union all select * from EsaTransSum_2
5/
create view EsaTransSum_5 as select * from EsaTransSum_4 union all select * from EsaTransSum_3
解决方案 »
- 创建的文本已存在，就在文件名后面加（2）（3）这样怎么实现
- makecert.exe和SignCode.Exe这两个软件哪里有？
- 请问各位：在输出WORD文档时，进行页面设置一直返回错误码，262。在写代码时应该怎样避免该错误重复出现？
- WINDWOS的关机程序源码？在线等待
- 请问：在VB中如何实现把数据库的一个表进行复制一遍，复制到同一个数据库中，只是改一个表名。
- 请问在vb中找匹配某字符位置的函数是什么？
- 请教 一些问题 属于菜鸟集级的
- 向高手请教一个很菜的问题，请指教！
- 在线等待,关于给模块中的变量赋值!!!
- 关于时间查询的问题?
- 如何清理火狐浏览器历史访问记录和站点登陆ID信息?
- 用脚本实现读取dat文件并导入到excel！急
6/
create view EsaTransSum_6 as
SELECT s.card_name,
s.cardno,
m.mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
s.acq_term_id,
s.agent_mcht_id,
s.pnt_trans_num_successful,
s.pnt_trans_amt_successful,
s.pnt_trans_acq_fee_successful,
s.pnt_trans_iss_fee_successful,
s.ncr_trans_num_successful,
s.ncr_trans_amt_successful,
s.ncr_trans_acq_fee_successful,
s.ncr_trans_iss_fee_successful,
s.per_trans_num_successful,
s.per_trans_amt_successful,
s.per_trans_acq_fee_successful,
s.per_trans_iss_fee_successful,
s.psr_trans_num_successful,
s.psr_trans_amt_successful,
s.psr_trans_acq_fee_successful,
s.psr_trans_iss_fee_successful,
s.rsa_trans_num_successful,
s.rsa_trans_amt_successful,
s.rsa_trans_acq_fee_successful,
s.rsa_trans_iss_fee_successful,
s.tsi_trans_num_successful,
s.tsi_trans_amt_successful,
s.tsi_trans_acq_fee_successful,
s.tsi_trans_iss_fee_successful,
s.tso_trans_num_successful,
s.tso_trans_amt_successful,
s.tso_trans_acq_fee_successful,
s.tso_trans_iss_fee_successful,
s.psn_trans_num_successful,
s.psn_trans_amt_successful,
s.psn_trans_acq_fee_successful,
s.psn_trans_iss_fee_successful
FROM EsaTransSum_5 s,
l2_mcht_three m
where m.mcht_id=s.acq_mcht_id
and m.sett_flag in(select m.sett_flag from l2_mcht_three where m.term_id =s.acq_term_id )
group by s.card_name, s.cardno, m.mcht_name, s.iss_mcht_id, s.acq_mcht_id, s.acq_term_id, s.agent_mcht_id, s.pnt_trans_num_successful, s.pnt_trans_amt_successful, s.pnt_trans_acq_fee_successful, s.pnt_trans_iss_fee_successful, s.ncr_trans_num_successful, s.ncr_trans_amt_successful,s.ncr_trans_acq_fee_successful, s.ncr_trans_iss_fee_successful, s.per_trans_num_successful, s.per_trans_amt_successful, s.per_trans_acq_fee_successful, s.per_trans_iss_fee_successful, s.psr_trans_num_successful, s.psr_trans_amt_successful, s.psr_trans_acq_fee_successful, s.psr_trans_iss_fee_successful, s.rsa_trans_num_successful, s.rsa_trans_amt_successful, s.rsa_trans_acq_fee_successful, s.rsa_trans_iss_fee_successful, s.tsi_trans_num_successful, s.tsi_trans_amt_successful, s.tsi_trans_acq_fee_successful, s.tsi_trans_iss_fee_successful, s.tso_trans_num_successful,s.tso_tra
ns_amt_successful, s.tso_trans_acq_fee_successful , s.tso_trans_iss_fee_successful, s.psn_trans_num_successful, s.psn_trans_amt_successful, s.psn_trans_acq_fee_successful, s.psn_trans_iss_fee_successful
7/
create view EsaTransSum_7 as
SELECT s.card_name,
s.cardno,
m.mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
s.acq_term_id,
s.agent_mcht_id,
s.pnt_trans_num_successful,
s.pnt_trans_amt_successful,
s.pnt_trans_acq_fee_successful,
s.pnt_trans_iss_fee_successful,
s.ncr_trans_num_successful,
s.ncr_trans_amt_successful,
s.ncr_trans_acq_fee_successful,
s.ncr_trans_iss_fee_successful,
s.per_trans_num_successful,
s.per_trans_amt_successful,
s.per_trans_acq_fee_successful,
s.per_trans_iss_fee_successful,
s.psr_trans_num_successful,
s.psr_trans_amt_successful,
s.psr_trans_acq_fee_successful,
s.psr_trans_iss_fee_successful,
s.rsa_trans_num_successful,
s.rsa_trans_amt_successful,
s.rsa_trans_acq_fee_successful,
s.rsa_trans_iss_fee_successful,
s.tsi_trans_num_successful,
s.tsi_trans_amt_successful,
s.tsi_trans_acq_fee_successful,
s.tsi_trans_iss_fee_successful,
s.tso_trans_num_successful,
s.tso_trans_amt_successful,
s.tso_trans_acq_fee_successful,
s.tso_trans_iss_fee_successful,
s.psn_trans_num_successful,
s.psn_trans_amt_successful,
s.psn_trans_acq_fee_successful,
s.psn_trans_iss_fee_successful
FROM EsaTransSum_5 s,
l2_mcht_three m
where m.mcht_id=s.acq_mcht_id
and s.acq_term_id not in(select m.term_id from l2_mcht_three m where m.term_id=s.acq_term_id)
and m.top_mcht_id ='0000'
group by s.card_name, s.cardno, m.mcht_name, s.iss_mcht_id, s.acq_mcht_id, s.acq_term_id, s.agent_mcht_id, s.pnt_trans_num_successful, s.pnt_trans_amt_successful, s.pnt_trans_acq_fee_successful, s.pnt_trans_iss_fee_successful, s.ncr_trans_num_successful, s.ncr_trans_amt_successful,s.ncr_trans_acq_fee_successful, s.ncr_trans_iss_fee_successful, s.per_trans_num_successful, s.per_trans_amt_successful, s.per_trans_acq_fee_successful, s.per_trans_iss_fee_successful, s.psr_trans_num_successful, s.psr_trans_amt_successful, s.psr_trans_acq_fee_successful, s.psr_trans_iss_fee_successful, s.rsa_trans_num_successful, s.rsa_trans_amt_successful, s.rsa_trans_acq_fee_successful, s.rsa_trans_iss_fee_successful, s.tsi_trans_num_successful, s.tsi_trans_amt_successful, s.tsi_trans_acq_fee_successful, s.tsi_trans_iss_fee_successful, s.ts
o_trans_num_successful,s.tso_trans_amt_successful, s.tso_trans_acq_fee_successful, s.tso_trans_iss_fee_successful, s.psn_trans_num_successful, s.psn_trans_amt_successful, s.psn_trans_acq_fee_successful, s.psn_trans_iss_fee_successful
8/
create view EsaTransSum9 as select * from EsaTransSum_6 union all select * from EsaTransSum_7
9/
create view EsaTransSum11 as
SELECT s.card_name,
s.cardno,
rtrim(m.mcht_name)+'('+m.term_id +')' as mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
m.term_id,
s.agent_mcht_id,
s.pnt_trans_num_successful,
s.pnt_trans_amt_successful,
s.pnt_trans_acq_fee_successful,
s.pnt_trans_iss_fee_successful,
s.ncr_trans_num_successful,
s.ncr_trans_amt_successful,
s.ncr_trans_acq_fee_successful,
s.ncr_trans_iss_fee_successful,
s.per_trans_num_successful,
s.per_trans_amt_successful,
s.per_trans_acq_fee_successful,
s.per_trans_iss_fee_successful,
s.psr_trans_num_successful,
s.psr_trans_amt_successful,
s.psr_trans_acq_fee_successful,
s.psr_trans_iss_fee_successful,
s.rsa_trans_num_successful,
s.rsa_trans_amt_successful,
s.rsa_trans_acq_fee_successful,
s.rsa_trans_iss_fee_successful,
s.tsi_trans_num_successful,
s.tsi_trans_amt_successful,
s.tsi_trans_acq_fee_successful,
s.tsi_trans_iss_fee_successful,
s.tso_trans_num_successful,
s.tso_trans_amt_successful,
s.tso_trans_acq_fee_successful,
s.tso_trans_iss_fee_successful,
s.psn_trans_num_successful,
s.psn_trans_amt_successful,
s.psn_trans_acq_fee_successful,
s.psn_trans_iss_fee_successful
FROM EsaTransSum9 s,
l2_mcht_three m
where m.mcht_id=s.iss_mcht_id
and m.sett_flag in(select m.sett_flag from l2_mcht_three m where m.term_id =s.acq_term_id )
and left(m.term_id,2)='96'
10/
create view EsaTransSum_8 as
SELECT s.card_name,
s.cardno,
m.mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
s.acq_term_id,
s.agent_mcht_id,
s.pnt_trans_num_successful,
s.pnt_trans_amt_successful,
s.pnt_trans_acq_fee_successful,
s.pnt_trans_iss_fee_successful,
s.ncr_trans_num_successful,
s.ncr_trans_amt_successful,
s.ncr_trans_acq_fee_successful,
s.ncr_trans_iss_fee_successful,
s.per_trans_num_successful,
s.per_trans_amt_successful,
s.per_trans_acq_fee_successful,
s.per_trans_iss_fee_successful,
s.psr_trans_num_successful,
s.psr_trans_amt_successful,
s.psr_trans_acq_fee_successful,
s.psr_trans_iss_fee_successful,
s.rsa_trans_num_successful,
s.rsa_trans_amt_successful,
s.rsa_trans_acq_fee_successful,
s.rsa_trans_iss_fee_successful,
s.tsi_trans_num_successful,
s.tsi_trans_amt_successful,
s.tsi_trans_acq_fee_successful,
s.tsi_trans_iss_fee_successful,
s.tso_trans_num_successful,
s.tso_trans_amt_successful,
s.tso_trans_acq_fee_successful,
s.tso_trans_iss_fee_successful,
s.psn_trans_num_successful,
s.psn_trans_amt_successful,
s.psn_trans_acq_fee_successful,
s.psn_trans_iss_fee_successful
FROM EsaTransSum_5 s,
l2_mcht m
where s.acq_mcht_id=m.mcht_id
and s.acq_term_id =m.term_id
group by s.card_name, s.cardno, m.mcht_name, s.iss_mcht_id, s.acq_mcht_id, s.acq_term_id, s.agent_mcht_id, s.pnt_trans_num_successful, s.pnt_trans_amt_successful, s.pnt_trans_acq_fee_successful, s.pnt_trans_iss_fee_successful, s.ncr_trans_num_successful, s.ncr_trans_amt_successful,s.ncr_trans_acq_fee_successful, s.ncr_trans_iss_fee_successful, s.per_trans_num_successful, s.per_trans_amt_successful, s.per_trans_acq_fee_successful, s.per_trans_iss_fee_successful, s.psr_trans_num_successful, s.psr_trans_amt_successful, s.psr_trans_acq_fee_successful, s.psr_trans_iss_fee_successful, s.rsa_trans_num_successful, s.rsa_trans_amt_successful, s.rsa_trans_acq_fee_successful, s.rsa_trans_iss_fee_successful, s.tsi_trans_num_successful, s.tsi_trans_amt_successful, s.tsi_trans_acq_fee_successful, s.tsi_trans_iss_fee_successful, s.tso_trans_num_successful,s.tso_trans_amt_successful,
s.tso_trans_acq_fee_successful , s.tso_trans_iss_fee_successful, s.psn_trans_num_successful, s.psn_trans_amt_successful, s.psn_trans_acq_fee_successful, s.psn_trans_iss_fee_successful
11/
create view EsaTransSum8 as
SELECT s.card_name,
s.cardno,
m.mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
m.term_id,
s.agent_mcht_id,
s.pnt_trans_num_successful,
s.pnt_trans_amt_successful,
s.pnt_trans_acq_fee_successful,
s.pnt_trans_iss_fee_successful,
s.ncr_trans_num_successful,
s.ncr_trans_amt_successful,
s.ncr_trans_acq_fee_successful,
s.ncr_trans_iss_fee_successful,
s.per_trans_num_successful,
s.per_trans_amt_successful,
s.per_trans_acq_fee_successful,
s.per_trans_iss_fee_successful,
s.psr_trans_num_successful,
s.psr_trans_amt_successful,
s.psr_trans_acq_fee_successful,
s.psr_trans_iss_fee_successful,
s.rsa_trans_num_successful,
s.rsa_trans_amt_successful,
s.rsa_trans_acq_fee_successful,
s.rsa_trans_iss_fee_successful,
s.tsi_trans_num_successful,
s.tsi_trans_amt_successful,
s.tsi_trans_acq_fee_successful,
s.tsi_trans_iss_fee_successful,
s.tso_trans_num_successful,
s.tso_trans_amt_successful,s.
tso_trans_acq_fee_successful,
s.tso_trans_iss_fee_successful,
s.psn_trans_num_successful,
s.psn_trans_amt_successful,
s.psn_trans_acq_fee_successful,
s.psn_trans_iss_fee_successful
FROM EsaTransSum_8 s,
l2_mcht m
where s.acq_mcht_id=m.mcht_id
and left(m.term_id,2)='99'
group by s.card_name, s.cardno, m.mcht_name, s.iss_mcht_id, s.acq_mcht_id, m.term_id, s.agent_mcht_id, s.pnt_trans_num_successful, s.pnt_trans_amt_successful, s.pnt_trans_acq_fee_successful, s.pnt_trans_iss_fee_successful, s.ncr_trans_num_successful, s.ncr_trans_amt_successful,s.ncr_trans_acq_fee_successful, s.ncr_trans_iss_fee_successful, s.per_trans_num_successful, s.per_trans_amt_successful, s.per_trans_acq_fee_successful, s.per_trans_iss_fee_successful, s.psr_trans_num_successful, s.psr_trans_amt_successful, s.psr_trans_acq_fee_successful, s.psr_trans_iss_fee_successful, s.rsa_trans_num_successful, s.rsa_trans_amt_successful, s.rsa_trans_acq_fee_successful, s.rsa_trans_iss_fee_successful, s.tsi_trans_num_successful, s.tsi_trans_amt_successful, s.tsi_trans_acq_fee_successful, s.tsi_trans_iss_fee_successful, s.tso_trans_num_successful,s.tso_trans_amt_successful,
s.tso_trans_acq_fee_successful, s.tso_trans_iss_fee_successful, s.psn_trans_num_successful, s.psn_trans_amt_successful, s.psn_trans_acq_fee_successful, s.psn_trans_iss_fee_successful
12/
create view EsaTransSum_10 as
SELECT s.card_name,
s.cardno,
rtrim(s.mcht_name)+'('+s.term_id +')' as mcht_name,
s.iss_mcht_id,
s.acq_mcht_id,
s.term_id,
s.agent_mcht_id,
s.pnt_trans_num_successful,
s.pnt_trans_amt_successful,
s.pnt_trans_acq_fee_successful,
s.pnt_trans_iss_fee_successful,
s.ncr_trans_num_successful,
s.ncr_trans_amt_successful,
s.ncr_trans_acq_fee_successful,
s.ncr_trans_iss_fee_successful,
s.per_trans_num_successful,
s.per_trans_amt_successful,
s.per_trans_acq_fee_successful,
s.per_trans_iss_fee_successful,
s.psr_trans_num_successful,
s.psr_trans_amt_successful,
s.psr_trans_acq_fee_successful,
s.psr_trans_iss_fee_successful,
s.rsa_trans_num_successful,
s.rsa_trans_amt_successful,
s.rsa_trans_acq_fee_successful,
s.rsa_trans_iss_fee_successful,
s.tsi_trans_num_successful,
s.tsi_trans_amt_successful,
s.tsi_trans_acq_fee_successful,
s.tsi_trans_iss_fee_successful,
s.tso_trans_num_successful,
s.tso_trans_amt_successful,
s.tso_trans_acq_fee_successful,
s.tso_trans_iss_fee_successful,
s.psn_trans_num_successful,
s.psn_trans_amt_successful,
s.psn_trans_acq_fee_successful,
s.psn_trans_iss_fee_successful
FROM EsaTransSum8 s
where (s.iss_mcht_id not in(select m.mcht_id from l2_mcht_three m))
group by s.card_name, s.cardno, s.mcht_name, s.iss_mcht_id, s.acq_mcht_id, s.term_id, s.agent_mcht_id, s.pnt_trans_num_successful, s.pnt_trans_amt_successful, s.pnt_trans_acq_fee_successful, s.pnt_trans_iss_fee_successful, s.ncr_trans_num_successful, s.ncr_trans_amt_successful,s.ncr_trans_acq_fee_successful, s.ncr_trans_iss_fee_successful, s.per_trans_num_successful, s.per_trans_amt_successful, s.per_trans_acq_fee_successful, s.per_trans_iss_fee_successful, s.psr_trans_num_successful, s.psr_trans_amt_successful, s.psr_trans_acq_fee_successful, s.psr_trans_iss_fee_successful, s.rsa_trans_num_successful, s.rsa_trans_amt_successful, s.rsa_trans_acq_fee_successful, s.rsa_trans_iss_fee_successful, s.tsi_trans_num_successful, s.tsi_trans_amt_successful, s.tsi_trans_acq_fee_successful, s.tsi_trans_iss_fee_successful, s.tso_trans_num_successful,s.tso_tran
s_amt_successful, s.tso_trans_acq_fee_successful, s.tso_trans_iss_fee_successful, s.psn_trans_num_successful, s.psn_trans_amt_successful, s.psn_trans_acq_fee_successful, s.psn_trans_iss_fee_successful
12/
create view EsaTransSum12 as select * from EsaTransSum11 union all select * from EsaTransSum_10
13/
create view EsaTransSum as
SELECT card_name,
mcht_name,
iss_mcht_id,
acq_mcht_id,
term_id,
agent_mcht_id,
SUM(pnt_trans_num_successful) AS pnt_trans_num_successful,
SUM(pnt_trans_amt_successful) AS pnt_trans_amt_successful,
SUM(pnt_trans_acq_fee_successful) AS pnt_trans_acq_fee_successful,
SUM(pnt_trans_iss_fee_successful) AS pnt_trans_iss_fee_successful,
SUM(ncr_trans_num_successful) AS ncr_trans_num_successful,
SUM(ncr_trans_amt_successful) AS ncr_trans_amt_successful,
SUM(ncr_trans_acq_fee_successful) AS ncr_trans_acq_fee_successful,
SUM(ncr_trans_iss_fee_successful) AS ncr_trans_iss_fee_successful,
SUM(per_trans_num_successful) AS per_trans_num_successful,
SUM(per_trans_amt_successful) AS per_trans_amt_successful,
SUM(per_trans_acq_fee_successful) AS per_trans_acq_fee_successful,
SUM(per_trans_iss_fee_successful) AS per_trans_iss_fee_successful,
SUM(psr_trans_num_successful) AS psr_trans_num_successful,
SUM(psr_trans_amt_successful) AS psr_trans_amt_successful,
SUM(psr_trans_acq_fee_successful) AS psr_trans_acq_fee_successful,
SUM(psr_trans_iss_fee_successful) AS psr_trans_iss_fee_successful,
SUM(rsa_trans_num_successful) AS rsa_trans_num_successful,
SUM(rsa_trans_amt_successful) AS rsa_trans_amt_successful,
SUM(rsa_trans_acq_fee_successful) AS rsa_trans_acq_fee_successful,
SUM(rsa_trans_iss_fee_successful) AS rsa_trans_iss_fee_successful,
SUM(tsi_trans_num_successful) AS tsi_trans_num_successful,
SUM(tsi_trans_amt_successful) AS tsi_trans_amt_successful,
SUM(tsi_trans_acq_fee_successful) AS tsi_trans_acq_fee_successful,
SUM(tsi_trans_iss_fee_successful) AS tsi_trans_iss_fee_successful,
SUM(tso_trans_num_successful) AS tso_trans_num_successful,
SUM(tso_trans_amt_successful) AS tso_trans_amt_successful,
SUM(tso_trans_acq_fee_successful) AS tso_trans_acq_fee_successful,
SUM(tso_trans_iss_fee_successful) AS tso_trans_iss_fee_successful,
SUM(psn_trans_num_successful) AS psn_trans_num_successful,
SUM(psn_trans_amt_successful) AS psn_trans_amt_successful,
SUM(psn_trans_acq_fee_successful) AS psn_trans_acq_fee_successful,
SUM(psn_trans_iss_fee_successful) As psn_trans_iss_fee_successful
From EsaTransSum12
GROUP BY card_name, mcht_name, iss_mcht_id, acq_mcht_id, term_id, agent_mcht_id