Question

  • Creator
    Topic
  • #2148299

    case when statement

    Locked

    by vemulakk ·

    Hi,

    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 CASE

    WHEN 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

All Answers

  • Author
    Replies
    • #2565650

      Clarifications

      by vemulakk ·

      In reply to case when statement

      Clarifications

    • #2567271

      Case is n’t making it go cartesian no where or

      by tony hopkinson ·

      In reply to case when statement

      join clause is doing that.

      Case operates on each row returned it doesn’t limit the number of rows returned.

Viewing 1 reply thread