oracle case when then else end用法

语法：

　　CASE search_expression

　　当表达1那么结果1

　　当表达2那么结果2

 

比如客户类型个人0，机构1，产品2对应的证件类型分别对应1018,1019,1355字典项。

下面举例说明具体实现：如何把某客户类型对应的证件类型翻译成中文。

SELECT T.VC_CUSTOMNAME,
       T1.VC_FUNDACCO,
       T2.VC_TRADEACCO,
       D2.C_CAPTION,
       T.C_CUSTTYPE,
       T.C_IDENTITYTYPE,
       F.C_CAPTION,
       T.VC_IDENTITYNO,
       D1.C_CAPTION,
       T3.VC_BANKACCO,
       T3.VC_BANKNAME,
       T3.VC_NAMEINBANK,
       T2.VC_ADDRESS,
       T2.VC_MOBILENO,
       T2.VC_ZIP
  FROM TCUSTINFO T
  LEFT JOIN TFUNDACCO T1
    ON T.VC_CUSTNO = T1.VC_CUSTNO
  LEFT JOIN TACCOINFO T2
    ON T1.VC_CUSTNO = T2.VC_CUSTNO
  LEFT JOIN TACCOBANK T3
    ON T2.VC_TRADEACCO = T3.VC_TRADEACCO
  LEFT JOIN (SELECT * FROM TDICTIONARY TD WHERE TD.L_KEYNO = '1014') D1
    ON T3.C_BANKNO = D1.C_KEYVALUE
  LEFT JOIN (SELECT * FROM TDICTIONARY TD WHERE TD.L_KEYNO = '1001') D2
    ON T.C_CUSTTYPE = D2.C_KEYVALUE
  LEFT JOIN (SELECT (CASE T.L_KEYNO
                      WHEN 1018 THEN
                       1
                      WHEN 1019 THEN
                       0
                      ELSE
                       2
                    END) L_KEYNO,
                    T.C_KEYVALUE,
                    T.C_CAPTION,
                    T.C_MODIFY,
                    T.C_MEMO,
                    T.VC_TRANSLATION,
                    T.L_ORDER
               FROM TDICTIONARY T
              WHERE T.L_KEYNO IN ('1018', '1019', '1355')) F
    ON T.C_CUSTTYPE = F.L_KEYNO
   AND T.C_IDENTITYTYPE = F.C_KEYVALUE;