select distinct pm.policyno,
decode(pr.plancode, '0000', pr.riskcode, pr.plancode) as riskcode,
pr.startdate,
pr.endDate,
pm.cancelind,
pm.renewedInd,
pm.surrenderInd,
pm.validind,
pm.companycode
from GuPolicyMain pm, GuPolicyRisk pr
where pm.policyNo = pr.policyNo
AND (pm.coinsInd = '0')
AND (pm.cancelInd = '0')
AND (pm.renewedInd = '0')
AND (pm.surrenderInd = '0')
AND (pr.validInd = '1')
and pm.companyCode not in
('010601', '0106010097', '01060101', '0106010197')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') <=
to_char('2017-01-31 23:59:59')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') >=
to_char('2017-01-01 00:00:00')
and pm.businessType not in ('2', '3')
and pm.standardInd is null
and exists (select riskcode
from ggriskconfigvalue
where configcode = 'RENEWAL_NEWPROCESSIND'
and validInd = '1'
and riskcode = pr.riskcode)
and pr.plancode = '0000'
union all
select distinct pm.policyno,
decode(pr.plancode, '0000', pr.riskcode, pr.plancode) as riskcode,
pr.startdate,
pr.endDate,
pm.cancelind,
pm.renewedInd,
pm.surrenderInd,
pm.validind,
pm.companycode
from GuPolicyMain pm, GuPolicyRisk pr
where pm.policyNo = pr.policyNo
AND (pm.coinsInd = '0')
AND (pm.cancelInd = '0')
AND (pm.renewedInd = '0')
AND (pm.surrenderInd = '0')
AND (pr.validInd = '1')
and pm.companyCode not in
('010601', '0106010097', '01060101', '0106010197')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') <=
to_char('2017-01-31 23:59:59')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') >=
to_char('2017-01-01 00:00:00')
and pm.businessType not in ('2', '3')
and pm.standardInd is null
and pr.plancode <> '0000'
and exists (select riskcode
from ggriskconfigvalue
where configcode = 'RENEWAL_NEWPROCESSIND'
and validInd = '1'
and riskcode = pr.planCode)
union all
select distinct pm.policyno,
decode(pr.plancode, '0000', pr.riskcode, pr.plancode) as riskcode,
pr.startdate,
pr.endDate,
pm.cancelind,
pm.renewedInd,
pm.surrenderInd,
pm.validind,
pm.companycode
from GuPolicyMain pm, GuPolicyRisk pr
where pm.policyNo = pr.policyNo
AND (pm.coinsInd = '0')
AND (pm.cancelInd = '0')
AND (pm.renewedInd = '0')
AND (pm.surrenderInd = '0')
AND (pr.validInd = '1')
and pm.companyCode in
('010601', '0106010097', '01060101', '0106010197')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') <=
to_char('2017-02-28 23:59:59')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') >=
to_char('2017-02-01 00:00:00')
and pm.businessType not in ('2', '3')
and pm.standardInd is null
and exists (select riskcode
from ggriskconfigvalue
where configcode = 'RENEWAL_NEWPROCESSIND'
and validInd = '1'
and riskcode = pr.riskcode)
and pr.plancode = '0000'
union all
select distinct pm.policyno,
decode(pr.plancode, '0000', pr.riskcode, pr.plancode) as riskcode,
pr.startdate,
pr.endDate,
pm.cancelind,
pm.renewedInd,
pm.surrenderInd,
pm.validind,
pm.companycode
from GuPolicyMain pm, GuPolicyRisk pr
where pm.policyNo = pr.policyNo
AND (pm.coinsInd = '0')
AND (pm.cancelInd = '0')
AND (pm.renewedInd = '0')
AND (pm.surrenderInd = '0')
AND (pr.validInd = '1')
and pm.companyCode in
('010601', '0106010097', '01060101', '0106010197')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') <=
to_char('2017-02-28 23:59:59')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') >=
to_char('2017-02-01 00:00:00')
and pm.businessType not in ('2', '3')
and pm.standardInd is null
and pr.plancode <> '0000'
and exists (select riskcode
from ggriskconfigvalue
where configcode = 'RENEWAL_NEWPROCESSIND'
and validInd = '1'
and riskcode = pr.planCode)
order by companycode, riskCode, policyno
uj5u.com熱心網友回復：
求大神過來幫忙看看啊
uj5u.com熱心網友回復：
select distinct pm.policyno,
decode(pr.plancode, '0000', pr.riskcode, pr.plancode) as riskcode,
pr.startdate,
pr.endDate,
pm.cancelind,
pm.renewedInd,
pm.surrenderInd,
pm.validind,
pm.companycode
from GuPolicyMain pm, GuPolicyRisk pr
where pm.policyNo = pr.policyNo
AND (pm.coinsInd = '0')
AND (pm.cancelInd = '0')
AND (pm.renewedInd = '0')
AND (pm.surrenderInd = '0')
AND (pr.validInd = '1')
and pm.companyCode not in
('010601', '0106010097', '01060101', '0106010197')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') <=
to_char('2017-02-28 23:59:59')
and to_char(pr.enddate, 'yyyy-mm-dd hh:mm:dd') >=
to_char('2017-01-01 00:00:00')
and pm.businessType not in ('2', '3')
and pm.standardInd is null
and exists (select riskcode
from ggriskconfigvalue
where configcode = 'RENEWAL_NEWPROCESSIND'
and validInd = '1'
and riskcode = pr.riskcode)
order by companycode, riskCode, policyno;
看了下，先改成這樣看看執行計劃
uj5u.com熱心網友回復：
你的4個union all子陳述句是不是只有 pr.plancode <> '0000' 和pr.plancode = '0000'的區別，還有日期上的區別？uj5u.com熱心網友回復：
其實這么算可能有一點點的問題，因為原陳述句是先distinct后再union all，2個union all子句中查出有相同的行值，不會剔除重復。這個陳述句直接查后會distinct關鍵字剔除了重復。
具體要看你的欄位 decode(pr.plancode, '0000', pr.riskcode, pr.plancode) ，會不會出現0000 和 riskcode的值的情況
uj5u.com熱心網友回復：
還有pm.companyCode 這個 業務要求根據這個不同 取不同日期區間，同時還要考慮pr.plancode 不同uj5u.com熱心網友回復：
pm.companyCode 4個陳述句的條件不都是一樣的么？uj5u.com熱心網友回復：
就是說 當pm.companyCode in 的時候 日期要在2017年1月里；not in的時候 月份在2017年2月里，同時考慮pr的plancode，plancode有一種情況是0000.這時候取riskcode跟ggriskconfigvalue關聯；若是pr.plancode<>'0000' 則取plancode跟ggriskconfigvalue做關聯，仔細看肯定不一樣的。。。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/112110.html
標籤：開發