case when的判断顺序_Oracle 查询中判断语句的写法case when then else end

在查询时可能会根据某个条件来判断显示数据，此时就用到case语句

下面是一个查询示例：

SELECT distinct a.certiType,

a.certiNo,

a.policyNo,

d.riskCode,

a.agentCode,

(case

when 'P' = a.certitype

then b.sumpremium

else c.chgpremium

end ) as sumpremium,

(select sacmain.disrate

from sacmain

where policyNo = a.policyno) as rate,

d.confirmNo,

d.uploadFlag

FROM sarealpayfee a,

sacmain b,

sapmain c,

sapolicyuploadplatformlog d,

sacitemcar e

WHERE a.policyno = b.policyno(+)

and a.policyno = e.policyno(+)

and a.certino = c.endorseno(+)

and a.certino = d.certino

and a.agentCode is not null

and nvl(d.uploadFlag, 0) in ('0', '1')

and a.accountno = '2120151103445'

and a.riskCode in ('0332', '0335')

and nvl(e.usenaturecode, 0) not in

('309009', '309008', '301006001', '301006002')

and nvl(a.chagFlag, 0) <> '2'

and (d.settleno is null or d.settleno = '2120151103445')