CASE WHEN .... THEN END 的用法

WITH HD_APPLY_ALL AS
(SELECT '产品账户组合' AS OPERATE_MADAUL,
T.OP_TYPE,
T.COMBINA_FUND_STATUS AS OPERATE_OBJECT_STATUS,
'产品复核' AS KEY_WORD,
T.OP_ID,
'[产品] ' || T.COMBINA_FUND_CODE1 ||'_'|| T.COMBINA_FUND_NAME AS OPERATE_OBJECT,
T.OP_USER,
T.OP_DATE,
TO_CHAR(T.OP_TIME,'000000') AS OP_TIME
FROM EA_IUOP.ACC_COMBINA_FUND_APPLY T
WHERE T.CHECK_STATUS = '0'
AND EXISTS (SELECT 1
FROM VW_DM_SYS_BUSI_DEPT Q
WHERE Q.BUSI_DEPT_CODE = T.BUSI_DEPT_CODE
AND Q.USER_CODE = '21')
UNION ALL
SELECT '产品账户组合' AS OPERATE_MADAUL,
T1.OP_TYPE,
T1.SUB_COMBINA_FUND_STATUS AS OPERATE_OBJECT_STATUS,
'子产品复核' AS KEY_WORD,
T1.OP_ID,
'[子产品账户] ' || T1.SUB_COMBINA_CODE ||'_'|| T1.SUB_COMBINA_NAME AS OPERATE_OBJECT,
T1.OP_USER,
T1.OP_DATE,
TO_CHAR(T1.OP_TIME,'000000') AS OP_TIME
FROM EA_IUOP.ACC_SUB_COMBINA_FUND_APPLY T1
WHERE T1.CHECK_STATUS = '0'
AND EXISTS (SELECT 1
FROM VW_DM_SYS_BUSI_DEPT Q
WHERE Q.BUSI_DEPT_CODE = T1.BUSI_DEPT_CODE
AND Q.USER_CODE = '21')
UNION ALL
SELECT '产品账户组合' AS OPERATE_MADAUL,
T2.OP_TYPE,
T2.FUND_ACC_STATUS AS OPERATE_OBJECT_STATUS,
'资金账户复核' AS KEY_WORD,
T2.OP_ID,
'[资金账户] ' || T2.FUND_ACC1 ||'_'|| T2.FUND_ACC_NAME AS OPERATE_OBJECT,
T2.OP_USER,
T2.OP_DATE,
TO_CHAR(T2.OP_TIME,'000000') AS OP_TIME
FROM EA_IUOP.ACC_FUND_ACC_APPLY T2
WHERE T2.CHECK_STATUS = '0'
AND EXISTS (SELECT 1
FROM VW_DM_SYS_BUSI_DEPT Q
WHERE Q.BUSI_DEPT_CODE = T2.BUSI_DEPT_CODE
AND Q.USER_CODE = '21')
UNION ALL
SELECT '产品账户组合' AS OPERATE_MADAUL,
T3.OP_TYPE,
T3.ACC_STATUS AS OPERATE_OBJECT_STATUS,
'交易账户复核' AS KEY_WORD,
T3.OP_ID,
'[交易账户] ' || T3.TRADE_ACC1 ||'_'|| T3.TRADE_ACC_NAME AS OPERATE_OBJECT,
T3.OP_USER,
T3.OP_DATE,
TO_CHAR(T3.OP_TIME,'000000') AS OP_TIME
FROM EA_IUOP.ACC_TRADE_ACC_APPLY T3
WHERE T3.CHECK_STATUS = '0'
AND EXISTS (SELECT 1
FROM VW_DM_SYS_BUSI_DEPT Q
WHERE Q.BUSI_DEPT_CODE = T3.BUSI_DEPT_CODE
AND Q.USER_CODE = '21')
UNION ALL
SELECT '证券账户' AS OPERATE_MADAUL,
D1.OP_TYPE,
D1.INV_ACC_STATUS AS OPERATE_OBJECT_STATUS,
'证券账户复核' AS KEY_WORD,
D1.OP_ID,
'[证券账户] ' || D1.INV_ACC ||'_'|| D1.INV_NAME AS OPERATE_OBJECT,
D1.OP_USER,
D1.OP_DATE,
TO_CHAR(D1.OP_TIME,'000000') AS OP_TIME
FROM EA_IUOP.Acc_inv_acc_apply D1
WHERE D1.CHECK_STATUS = '0'
AND EXISTS (SELECT 1 FROM (SELECT A.BUSI_DEPT_CODE
FROM VW_DM_SYS_BUSI_DEPT A
WHERE A.USER_CODE = '21')k
WHERE 1=1
AND INSTR( D1.BUSI_DEPT_CODE,k.BUSI_DEPT_CODE) > 0 )
)
SELECT K.OPERATE_MADAUL,
K.OP_TYPE,
K.OPERATE_OBJECT_STATUS,
K.KEY_WORD,
K.OP_ID,
K.OPERATE_OBJECT,
K.OP_USER,
K.OP_DATE,
K.OP_TIME,
(CASE
WHEN K.KEY_WORD='产品复核' AND K.OP_TYPE = '2' AND K.OPERATE_OBJECT_STATUS = '1' THEN '5'
WHEN K.KEY_WORD='子产品复核' AND K.OP_TYPE = '2' AND K.OPERATE_OBJECT_STATUS = '1' THEN '5'
WHEN K.KEY_WORD='资金账户复核' AND K.OP_TYPE = '2' AND K.OPERATE_OBJECT_STATUS = '1' THEN '4'
WHEN K.KEY_WORD='资金账户复核' AND K.OP_TYPE = '2' AND K.OPERATE_OBJECT_STATUS = '2' THEN '5'
WHEN K.KEY_WORD='交易账户复核' AND K.OP_TYPE = '2' AND K.OPERATE_OBJECT_STATUS = '1' THEN '4'
WHEN K.KEY_WORD='交易账户复核' AND K.OP_TYPE = '2' AND K.OPERATE_OBJECT_STATUS = '2' THEN '5'
WHEN K.KEY_WORD='证券账户复核' AND K.OP_TYPE = '2' AND K.OPERATE_OBJECT_STATUS = '1' THEN '4'
WHEN K.KEY_WORD='证券账户复核' AND K.OP_TYPE = '2' AND K.OPERATE_OBJECT_STATUS = '2' THEN '5' END) OP_TYPE_FINAL
FROM HD_APPLY_ALL K
WHERE 1=1