Nested Case Statement Alternative/Rule Engine

  • Ray Dai

    Default port

    Points: 1408

    I inherited some ugly nested case statements. I would like to see if there are any alternatives so they are more readable and maintainable.

    Here is some simplified code

    create table #tblValues (val1 varchar(20), val2 int, val3 int, val4 datetime, val5 datetime, val6 int, val7 int)

    insert into #tblValues

    select null, 1, 150, '01/11/2010', '09/25/2010', 50, 1 union all

    select 'Y', 1, 150, '01/11/2010', '09/25/2010', 50, 1 union all

    select null, 0, 150, '01/12/2010', '09/25/2010', 50, 1 union all

    select null, 1, 150, '01/12/2010', '01/13/2010', 60, 1

    Then the SELECT

    select

    Case When Isnull(val1,'N') = 'N' Then

    Case When val2 = 1 Then

    Case When val3 is not null Then

    CASE WHEN (val4 IS NOT NULL) AND (val5 IS NOT NULL) THEN

    CASE WHEN val4 < '01/12/2010' THEN

    CASE WHEN DATEDIFF(D, val4, val5)>=65 THEN 10

    ELSE

    CASE WHEN val6 >= 120 THEN 9 -- days < 65 & minutes are greater than 120 so they get 1/2 price

    ELSE 11 -- mintues less than 120

    END

    END

    ELSE -- val4 >= '01/12/2010' --Version 1.4 New rules apply

    CASE WHEN DATEDIFF(D, val4, val5)>=7 THEN 10 --Version 1.4

    ELSE

    CASE WHEN val6 >= 60 THEN 9 -- days < 7 & minutes are greater than 60 so they get 1/2 price

    ELSE 11 -- mintues less than 60

    END

    END --Version 1.4

    END

    ELSE -- val4 IS NULL AND val5 IS NOT NULL --Version 1.1

    Case When val7 in (0,1,5) Then

    Case When val6 >= 120 Then 4 -- minutes are greater than 120

    Else 3 -- mintues less than 120

    End

    Else Case When val7 in (2,3) Then 5

    Else Case When val7 in (4) Then 6

    Else 7 -- invalid Code

    End

    End

    End

    END -- val4 IS NULL AND val5 IS NOT NULL --Version 1.1

    Else 8 -- unit price record not found

    End

    Else 2 -- Not a phone

    End

    Else 1 -- previously issued an check

    End As RuleResult, *

    from #tblValues

    My eyes hurt just looking at this.

    I have looked into coalesce but I am not sure it can handle something so complex, I'd be handling nested coalesce statements instead of nested case statements.

    I also read the articles in this post about rule engines

    But I don't understand the articles fully and they seem overly simplistic to me, just tables holding values and the operators are still defined in the code. I on the other hand, have ISNULL, IS NOT NULL, IN, and DateDiff to evaulate.

    I thought about:

    1) If I store just the values and not the operators into a table then then end results might not be much better than what I have now.

    2) If I push the operators/conditions in a table then I can only see doing some complex dynamic SQL to evaluate.

    Any thoughts? Thanks.

  • Hunterwood

    SSCrazy

    Points: 2012

    This is a start, can probably be done even more.

    select

    Case When not Isnull(val1,'N') = 'N' Then 1 -- previously issued an check

    When not val2 = 1 Then 2 -- Not a phone

    When val3 is null Then 8 -- unit price record not found

    Else

    CASE WHEN (val4 IS NOT NULL) AND (val5 IS NOT NULL) THEN

    CASE WHEN val4 < '01/12/2010' THEN

    CASE WHEN DATEDIFF(D, val4, val5)>=65 THEN 10

    WHEN val6 >= 120 THEN 9 -- days < 65 & minutes are greater than 120 so they get 1/2 price

    ELSE 11 -- mintues less than 120

    END

    ELSE -- val4 >= '01/12/2010' --Version 1.4 New rules apply

    CASE WHEN DATEDIFF(D, val4, val5)>=7 THEN 10 --Version 1.4

    WHEN val6 >= 60 THEN 9 -- days < 7 & minutes are greater than 60 so they get 1/2 price

    ELSE 11 -- mintues less than 60

    END --Version 1.4

    END

    ELSE -- val4 IS NULL AND val5 IS NOT NULL --Version 1.1

    Case When val7 in (0,1,5) Then

    Case When val6 >= 120 Then 4 -- minutes are greater than 120

    Else 3 -- mintues less than 120

    End

    Else Case When val7 in (2,3) Then 5

    When val7 in (4) Then 6

    Else 7 -- invalid Code

    End

    End

    END -- val4 IS NULL AND val5 IS NOT NULL --Version 1.1

    End As RuleResult, *

    from #tblValues

    There is no need to write "CASE WHEN ... THEN .. ELSE CASE WHEN ..." if the first statement is false, the CASE will test next.. so

    CASE WHEN <A> THEN ... ELSE

    CASE WHEN <B> THEN ...

    ELSE ...

    END

    END

    is equivalent with

    CASE WHEN <A> THEN ...

    WHEN <B> THEN ...

    ELSE ...

    END

    hope this start can help you to continue.

    /Markus

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply