Question
-
Topic
-
case when statement
LockedHi,
I have a if then else statement below.
IF ACC_Default_IND = ‘Y’ THEN status_cd = ‘4’;
ELSE IF ACC_Default_IND= ‘N’ and src_sys_financial_Acc_stat = ‘M’ OR ‘H’ THEN status_cd = ‘4’;
ELSE IF ACC_Default_IND= ‘N’ and src_sys_financial_Acc_stat = ‘B’ AND >=4 THEN status_cd = ‘4’;
ELSE IF ACC_Default_IND= ‘N’ and src_sys_financial_Acc_stat = ‘B’ AND =3 THEN status_cd = ‘3’;
ELSE IF ACC_Default_IND= ‘N’ and src_sys_financial_Acc_stat = ‘B’ AND =2 THEN status_cd = ‘2’;
ELSE IF ACC_Default_IND= ‘N’ and src_sys_financial_Acc_stat = ‘B’ AND =1 OR 0 THEN status_cd = ‘1’;
ELSE IF ACC_Default_IND= ‘N’ and src_sys_financial_Acc_stat = (‘D or E or I or J or K or L or N or R) THEN status_cd = ‘1’;ELSE status_cd = ’98’
Anybody can write case when statement for above condition.
i have written a case when but it is going to carteisan product , below is the code.
create table kumar as
SELECT CASEWHEN ACC_DEFAULT_IND = ‘Y’ THEN ‘4’
WHEN ACC_DEFAULT_IND = ‘N’ AND SRC_SYS_FINANCIAL_ACC_STAT IN (‘M’, ‘H’) THEN ‘4’
WHEN ACC_DEFAULT_IND = ‘N’ AND SRC_SYS_FINANCIAL_ACC_STAT = ‘B’ AND (INPUT(MTH_END_ARREARS_CYCLE_TYPE_CD,20.) >= 4) THEN ‘4’
WHEN ACC_DEFAULT_IND = ‘N’ AND SRC_SYS_FINANCIAL_ACC_STAT = ‘B’ AND INPUT(MTH_END_ARREARS_CYCLE_TYPE_CD,20.) = 3 THEN ‘3’
WHEN ACC_DEFAULT_IND = ‘N’ AND SRC_SYS_FINANCIAL_ACC_STAT = ‘B’ AND INPUT(MTH_END_ARREARS_CYCLE_TYPE_CD,20.) = 2 THEN ‘2’
WHEN ACC_DEFAULT_IND = ‘N’ AND SRC_SYS_FINANCIAL_ACC_STAT = ‘B’ AND INPUT(MTH_END_ARREARS_CYCLE_TYPE_CD,20.) = 1 OR 0 THEN ‘1’
WHEN ACC_DEFAULT_IND = ‘N’ AND SRC_SYS_FINANCIAL_ACC_STAT IN (‘D’, ‘E’, ‘I’, ‘J’, ‘K’, ‘L’, ‘N’, ‘R’) THEN ‘1’
ELSE ’98’
END
FROM STG_FIN_ACC_REG_PROFILE, stg_issue_acct_pool;
Anybody help me.
regards,
vkk
