sqloracle

How to rewrite an nested IFF statement to oracle case when statement


How to rewrite the following nested IFF statement to oracle Case when statement .

IIF(IN_RNK = 1,
IIF(IN_SUM > 2,IIF(IN_MTR_COVERAGE_NUMBER_A = 'A','ORIGINAL BASE','ORIGINAL RIDER'),IN_MTR_COVERAGE_NUMBER_A),
IN_MTR_COVERAGE_NUMBER_A)

Solution

  • Like this:

    CASE
    WHEN IN_RNK = 1
    THEN CASE
         WHEN IN_SUM > 2
         THEN CASE 
              WHEN IN_MTR_COVERAGE_NUMBER_A = 'A'
              THEN 'ORIGINAL BASE'
              ELSE 'ORIGINAL RIDER'
              END
         ELSE IN_MTR_COVERAGE_NUMBER_A
         END
    ELSE IN_MTR_COVERAGE_NUMBER_A
    END
    

    Which can be simplified to:

    CASE
    WHEN in_rnk = 1 AND in_sum > 2 AND in_mtr_coverage_number_a = 'A'
    THEN 'ORIGINAL BASE'
    WHEN in_rnk = 1 AND in_sum > 2
    THEN 'ORIGINAL RIDER'
    ELSE in_mtr_coverage_number_a
    END