Rewrite complex nested Case statement/Rule Engine

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rewrite complex nested Case statement/Rule Engine

Author

Topic

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-10 : 11:04:13

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 http://www.sqlservercentral.com/Forums/Topic275008-8-1.aspx 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:39:52

what's output you're expecting?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-10 : 13:02:57

Thanks for replying. If you ran the sample code, it will show the "RuleResult" column which is all that I am interested in. This is part of an even larger select statement that is determining what the "RuleResult" is, based on the other values.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-10 : 15:20:52

Without knowing all your business rules or other requirements outside this one CASE stament it's hard to say, but perhaps a rules engine might be the way to go. But, you can simplify your existing CASE statement a bit. Perhaps, takeing a setp back and re-thinking the logic could help..? Here is a quick mod that I did. You can probably refine it more, but it at least returns the same results as your posted query:

SELECT 
	CASE 
		WHEN COALESCE(val1, 'N') <> 'N' THEN 1 -- previously issued an check    
		WHEN val2 <> 1 THEN 2 -- Not a phone 
		WHEN val3 IS NULL THEN  8 -- unit price record not found        
		WHEN val4 IS NULL OR val5 IS NULL THEN
            Case 
				When val7 in (0,1,5) AND val6 >= 120 Then 4 -- minutes are greater than 120              
				When val7 in (0,1,5) THEN 3 -- mintues less than 120            
				When val7 in (2,3) Then 5  
				When val7 in (4) Then 6 
				Else 7 -- invalid Code            
			End    
		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 
			END   
		Else 7 -- invalid Code   ????         
        
	End  As RuleResult, *
from #tblValues
GO

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-10 : 15:40:31

Thank you very much. I will take a through look when things slows down a bit but it looks great. I guess clear thinking beats some clever solution most of the time!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources