<select id="getValuationSummary" parameterType="com.hundsun.amos.queryrpt.api.bean.AssetValueDTO"
resultMap="BaseAssetResultMap">
select *
from (with email as (select ff.fund_code,
ff.fund_id,
<if test="isPpos">
a.DATA_SOURCE,
a.publish_rate,
a.publish_state,
a.ACTIVE_VERIFYRATE,
</if>
ff.service_type,
ff.FUND_TYPE,
ff.TRANSACTION_SERVICE_TYPE,
apex.FUND_ORIGIN_INFO,
apex.SETTLEMENT_MODE,
ff.NON_STANDARD_PDT,
ff.SPECIAL_RISK_TYPE
from amos_pdt_fundinfo ff
<if test="isToasseessment">
right join AMOS_TOASSESSMENT_M atm
on ff.fund_code=atm.FUND_CODE
</if>
inner join AMOS_BASE_FUNDAUTHDETAL auth
on ff.fund_id = auth.fund_id
inner join AMOS_PDT_FUNDINFOEX apex
on ff.fund_id = apex.fund_id
<if test="isPpos">
left join
(
select s.DATA_SOURCE,s.publish_rate,s.publish_state,s.fund_id,s.ACTIVE_VERIFYRATE from AMOS_QYRPT_VALUATIONSET s
where not exists
(select 1 from (select fund_id from AMOS_QYRPT_VALUATIONSET group by fund_id having count(1) >1) t where
t.fund_id=s.fund_id)
union (
select f1.DATA_SOURCE,f1.publish_rate,f1.publish_state,f1.fund_id,f1.ACTIVE_VERIFYRATE from
(select DATA_SOURCE,publish_rate,fund_id,publish_state,ACTIVE_VERIFYRATE from AMOS_QYRPT_VALUATIONSET where DATA_SOURCE='WB')f1 left join
(select DATA_SOURCE,publish_rate,fund_id,publish_state,ACTIVE_VERIFYRATE from AMOS_QYRPT_VALUATIONSET where DATA_SOURCE='TG')f2
on f1.fund_id=f2.fund_id where f2.data_source is not null)
)a
on ff.fund_id = a.fund_id
</if>
where 1 = 1
<if test="opeator!=null and opeator!=''">
and
auth.user_id=#{opeator}
</if>
<if test="inputList != null and inputList.size()>0">
and
ff.fund_code in
<foreach collection="inputList" item="item" index="index" open="(" close=")" separator=",">
#{item.vcFundcode}
</foreach>
</if>
<if test="fundType != null and fundType != ''">
AND ff.FUND_TYPE IN
<foreach item="item" index="index" collection="fundType.split(',')" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="nonStandardPdt != null and nonStandardPdt != ''">
AND ff.NON_STANDARD_PDT IN
<foreach item="item" index="index" collection="nonStandardPdt.split(',')" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="settlementMode != null and settlementMode != ''">
AND apex.settlement_mode IN
<foreach item="item" index="index" collection="settlementMode.split(',')" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="serviceType != null and serviceType != ''">
AND
<foreach item="item" index="index" collection="serviceType.split(',')" open="(" separator="or" close=")">
instr(ff.service_type, #{item}) > 0
</foreach>
</if>
<if test="transactionServiceType != null and transactionServiceType != ''">
AND
<foreach item="item" index="index" collection="transactionServiceType.split(',')" open="(" separator="or" close=")">
instr(ff.TRANSACTION_SERVICE_TYPE, #{item}) > 0
</foreach>
</if>
<if test="specialRiskType != null and specialRiskType != ''">
AND
<foreach item="item" index="index" collection="specialRiskType.split(',')" open="(" separator="or" close=")">
instr(nvl(ff.SPECIAL_RISK_TYPE, '0'), #{item}) > 0
</foreach>
</if>
<if test="fundOriginInfo != null and fundOriginInfo != ''">
AND
<foreach item="fundOriginInfoItem" index="index" collection="fundOriginInfo.split(',')" open="(" separator="or" close=")">
instr(apex.FUND_ORIGIN_INFO, #{fundOriginInfoItem}) > 0
</foreach>
</if>
)
<if test="enSjly !='WB'">
select
h.vc_glr vc_glr,
<include refid="resultSql"/>
<if test="isPpos">
nvl(email.publish_rate,'1') plpl,
nvl( email.publish_state,'1') plzt,
decode(email.publish_rate,'3',email.ACTIVE_VERIFYRATE) actual_check_frequency,
nvl(email.ACTIVE_VERIFYRATE, decode(email.publish_rate,
'3',
least(foe.compare_Freq))
) sjplpl,
</if>
'托管估值' en_sjly_name,
'TG' en_sjly,
email.FUND_TYPE,
email.TRANSACTION_SERVICE_TYPE,
email.FUND_ORIGIN_INFO,
email.SETTLEMENT_MODE,
email.NON_STANDARD_PDT,
email.SPECIAL_RISK_TYPE
from tgcbs.tjjmrhjsj t
inner join email on t.vc_jjdm=email.fund_code
and
( INSTR(','||email.SERVICE_TYPE||',',',1,')>0 and
INSTR(','||email.SERVICE_TYPE||',',',2,')=0 and
INSTR(','||email.SERVICE_TYPE||',',',6,')=0 and instr(','||email.SERVICE_TYPE||',',',7,')=0)
<include refid="mfcodeSql"/>
left join tgcbs.ttmpgzb_index a on t.l_ztbh = a.l_ztbh
and t.d_rq = a.d_ywrq
left join (select l_ztbh, l_dzjg , d_begin
from (select zb.l_ztbh,
zb.l_dzjg,
zb.d_begin,
row_number() over(partition by zb.l_ztbh, zb.d_begin order by zb.d_scrq desc) rn
from tgcbs.tycdz_tgr_zb zb
where zb.l_sfbz = 0
<if test="startDate != null and startDate!= '' and endDate!=null and endDate!=''">
and
zb.d_begin between to_date(#{startDate},'yyyy-mm-dd') and to_date(#{endDate},'yyyy-mm-dd')
</if>
<if test="dateRate != null and dateRate!= ''">
<choose>
<when test="dateRateWeekMonthFlag != null and dateRateWeekMonthFlag != '' and dateRateWeekMonthFlag == '1'.toString()">
and to_char(zb.d_begin ,'yyyy-MM-dd') IN
<foreach collection="dateRateWeekMonthDates" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</when>
<otherwise>
and exists ( <include refid="tradeDateSql1"/> )
</otherwise>
</choose>
</if>
)
where rn = 1) b
on t.l_ztbh = b.l_ztbh and b.d_begin=t.d_rq ,
(select g.vc_mc vc_glrmc,
f.vc_glr,
f.vc_tgr,
f.vc_name,
f.l_fundid
from tgcbs.tfundinfo f
left join tgcbs.tglrxx g
on f.vc_glr = g.vc_bh
) h
<include refid="filterArgs"/>
<if test="isPpos">
and
nvl(email.publish_state, '1') in (nvl(a.l_sfqr, 0),'3')
<include refid="pposSql"/>
</if>
</if>
<if test="enSjly==null">
union all
</if>
<if test="enSjly !='TG'">
select
h.vc_glrmc vc_glr,
<include refid="resultSql"/>
<if test="isPpos">
nvl(email.publish_rate,'1') plpl,
nvl( email.publish_state,'3') plzt,
decode(email.publish_rate,'3',email.ACTIVE_VERIFYRATE) actual_check_frequency,
nvl(email.ACTIVE_VERIFYRATE, decode(email.publish_rate,
'3',
least(foe.compare_Freq))
) sjplpl,
</if>
'外包估值' en_sjly_name,
'WB' en_sjly,
email.FUND_TYPE,
email.TRANSACTION_SERVICE_TYPE,
email.FUND_ORIGIN_INFO,
email.SETTLEMENT_MODE,
email.NON_STANDARD_PDT,
email.SPECIAL_RISK_TYPE
from hsfa.tjjmrhjsj t
inner join email on t.vc_jjdm=email.fund_code
and (INSTR(','||email.SERVICE_TYPE||',',',2,')>0 or INSTR(','||email.SERVICE_TYPE||',',',6,')>0 or instr(','||email.SERVICE_TYPE||',',',7,')>0)
<include refid="mfcodeSql"/>
left join hsfa.ttmpgzb_index a on t.l_ztbh = a.l_ztbh
and t.d_rq = a.d_ywrq
left join (select l_ztbh, l_dzjg , d_begin
from (select zb.l_ztbh,
zb.l_dzjg,
zb.d_begin,
row_number() over(partition by zb.l_ztbh, zb.d_begin order by zb.d_scrq desc) rn
from hsfa.tycdz_zb zb
where zb.l_sfbz = 0
<if test="startDate != null and startDate!= '' and endDate!=null and endDate!=''">
and
zb.d_begin between to_date(#{startDate},'yyyy-mm-dd') and to_date(#{endDate},'yyyy-mm-dd')
</if>
<if test="dateRate != null and dateRate!= ''">
<choose>
<when test="dateRateWeekMonthFlag != null and dateRateWeekMonthFlag != '' and dateRateWeekMonthFlag == '1'.toString()">
and to_char(zb.d_begin ,'yyyy-MM-dd') IN
<foreach collection="dateRateWeekMonthDates" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</when>
<otherwise>
and exists ( <include refid="tradeDateSql1"/> )
</otherwise>
</choose>
</if>
)
where rn = 1) b
on t.l_ztbh = b.l_ztbh and b.d_begin=t.d_rq ,
(select g.vc_mc vc_glrmc,
f.vc_glr,
f.vc_tgr,
f.vc_name,
f.l_fundid
from hsfa.tfundinfo f
left join hsfa.tglrxx g
on f.vc_glr = g.vc_bh
) h
<include refid="filterArgs"/>
<if test="isPpos">
and
nvl(email.publish_state, '3') in (nvl(a.l_sfqr, 0),'3')
<include refid="pposSql"/>
</if>
</if>)
order by d_rq desc
</select>换成可测试的
本文探讨了在MyBatis中处理等于条件时常见的问题及解决方案。特别关注如何正确使用字符串转换来确保比较操作的准确性。
分类：
扫一扫
798
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
