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
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
解决方案 »
- dbms_sql解决动态SQL字符过长问题
- 存储过程中这种问题,你们是怎么处理的?知道的等于送分啦!
- 关于用户权限和imp/exp的问题?还有点小小的疑问,请指点!
- Ora10建用户的问题
- 除了在tnsnames里还有那个文件可以看配置过的数据源?
- 错误:ORA-00054:资源正忙,要求指定NOWAIT。问题出在哪里了?急!!!
- 一个关于sequence的难题,高手请进!^_^ 高分相赠
- 那里有WebDB下载啊.
- 不同数据库之间的数据传输
- SOS 索引表空间脱机了!!!!!!!!!
- 关于sqlldr导入忽略空行的问题
- 为啥报错PLS-00905: 对象 HUARUI2016.PROCUSERATTENDANCE 无效
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;看了下,先改成这样看看执行计划
这个语句直接查后会distinct关键字剔除了重复。
具体要看你的字段 decode(pr.plancode, '0000', pr.riskcode, pr.plancode) ,会不会出现0000 和 riskcode的值的情况