case --when

 

1.....................................

select 
case substr(JF.Jfssq,6,1)
when '4' then
substr(JF.Jfssq,1,4)|| '年'||'第四期'

when '3' then
substr(JF.Jfssq,1,4)||'年'||'第三期'

when '2' then
substr(JF.Jfssq,1,4)||'年'||'第二期'

when '1' then
substr(JF.Jfssq,1,4)||'年'||'第一期'
else ''
end as zq from dual

2.....................................

 

select * from dual

where bgxdm = 'jhzt'
and bgxxz in (0, 2, 9, 10, 18) --判断非源户 工会状态最近一次变成(0, 2, 9, 10, 18)
and czsj <![CDATA[<]]> to_date(case substr(#{ssq2,jdbcType=VARCHAR}, 5, 2)
when '01' then
substr(#{ssq2,jdbcType=VARCHAR}, 1, 4) || '0101'
when '02 ' then
substr(#{ssq2,jdbcType=VARCHAR}, 1, 4) || '0401'
when '03 ' then
substr(#{ssq2,jdbcType=VARCHAR}, 1, 4) || '0701'
when '04 ' then
substr(#{ssq2,jdbcType=VARCHAR}, 1, 4) || '1001'
end,
'yyyymmdd')