March 3, 2010 at 3:16 pm
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.
March 4, 2010 at 4:16 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply