Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2009
    Posts
    56

    Question Unanswered: Conversion of Case Construct to Decode and IF Conditions !!

    Hello ,

    I have the following case constructs , can any one provide me the equivalent of IF Condition implementation in Oracle

    1)
    CASE WHEN SRC_SYS_CD = 'NSC' AND NS_BNFT_FILE_CLMN_NUM BETWEEN 388 AND 394 THEN 'Y'
    WHEN SRC_SYS_CD <> 'NSC' THEN 'NA'
    ELSE 'N' END

    2)
    CASE WHEN SRC_SYS_CD = 'NSC' THEN
    CASE WHEN CDM_LOB_CD = 'FAC' AND NS_CAPN_CD = '55' THEN 'Y'
    WHEN CDM_LOB_CD = 'PROF' AND NS_CAPN_CD = '05' THEN 'Y' ELSE 'N' END
    END

    3)
    CASE WHEN SRC_SYS_CD = 'NSC' AND NS_BNFT_FILE_LVL_NUM = '56' THEN 'Y'
    WHEN SRC_SYS_CD = 'MMC' THEN 'Y'
    WHEN SRC_SYS_CD IN ('LFC', 'LPC') THEN 'NA'
    ELSE 'N' END


    Thanks /Mike

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Why?

    Why would you want decode/if construct if case is better?


    But here, knock yourself out:
    Code:
    -- 1)
       IF Src_Sys_Cd = 'NSC' AND Ns_Bnft_File_Clmn_Num BETWEEN 388 AND 394
    	THEN  Kk          := 'Y';
    	ELSIF Src_Sys_Cd <> 'NSC'
    	THEN  Kk          := 'NA';
    	ELSE  Kk          := 'N';
       END IF;
    
    --2)
    
       IF Src_Sys_Cd = 'NSC' AND Ns_Bnft_File_Clmn_Num BETWEEN 388 AND 394
       THEN 
    	IF Cdm_Lob_Cd = 'FAC' AND Ns_Capn_Cd = '55'
    	   THEN Kk          := 'Y';
    	   ELSIF Cdm_Lob_Cd = 'PROF' AND Ns_Capn_Cd = '05'
    	   THEN Kk          := 'Y';
    	   ELSE Kk          := 'N';
    	END IF;
       END IF;
    
    --3)
    
       IF Src_Sys_Cd = 'NSC' AND Ns_Bnft_File_Lvl_Num = '56'
    	THEN  Kk          := 'Y';
    	ELSIF Src_Sys_Cd = 'MMC'
    	THEN  Kk          := 'Y';
    	ELSIF Src_Sys_Cd IN ('LFC', 'LPC')
    	THEN  Kk          := 'NA';
    	ELSE  Kk          := 'N';
       END IF;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •