Suppose there is a business rule used to derive a value and due to how the business users need to update this, the logic must be held in a table and not a function.

Example of current function

 WHEN inputA IN (1,2,3) AND inputB NOT IN (55,66) THEN OUTPUT = 'HQ'
 WHEN inputA IN (3,6)   AND inputB     IN (27,44) THEN OUTPUT = 'Northern'
 WHEN inputC IN (6,4,1) AND inputB NOT IN (55,66) THEN OUTPUT = 'Eastern'
 etc.

I can build a table with a row for each combination e.g.

How then to model the "NOT IN" part of this?

Surely there is a way where I do not have to create all the 'exception' rows that would potentially need to be updated based on changing reference data?

CodePudding user response：

You could represent these expressions w/ a table where each row contained:

• Output
• Equals? (true or false)
• Input
• Value

which would represent "If (INPUT = VALUE) = EQUALS? then OUTPUT", except that you only need 1 satisfying row for a given OUTPUT/INPUT where EQUALS? is true, but all rows must be satisfied where EQUALS? is false.

This assumes your have only one "rule" for each distinct output.

CodePudding user response：

Here's an attempt at using a rule table with positive and negative rules.

The rule table stores the valids as integers, but the invalids in a string with delimiters. (not sure if json or xml would be better)

But having negative rules makes it really tricky.
Because it's easy to ignore a rule by adding another for the same output name.

Personally I think it's safer to use a UDF for this type of rules.

create table test (
 id int identity(1,1) primary key, 
 inputA int, 
 inputB int, 
 inputC int
)

create table output_areas (
 code varchar(2) primary key, 
 name varchar(30) not null
)

insert into output_areas values
  ('HQ', 'HQ')
, ('N', 'Northern'), ('E', 'Eastern')
, ('W', 'Westhern'), ('S', 'Southern')
, ('X', 'Extern'), ('Z', 'The Zone')


create table input_rules (
 id int identity(1,1) primary key, 
 output_area_code varchar(2) not null,
 relevance int not null default 0,
 inputA_valid int,
 inputB_valid int,
 inputC_valid int, 
 inputA_invalid varchar(100),
 inputB_invalid varchar(100),
 inputC_invalid varchar(100),
 foreign key (output_area_code) references output_areas(code)
)


insert into input_rules (output_area_code, relevance) 
values ('X', 0);

insert into input_rules (output_area_code, relevance, inputA_valid) 
values ('Z',10, 1);

insert into input_rules (output_area_code, relevance, inputA_valid, inputB_invalid) 
values
  ('HQ', 20, 1, '|55|56|')
, ('HQ', 20, 2, '|55|56|')
, ('HQ', 20, 3, '|55|56|')
;
insert into input_rules (output_area_code, relevance, inputA_valid, inputB_valid) 
values
  ('N', 30, 3, 27), ('N', 30, 3, 44)
, ('N', 30, 6, 27), ('N', 30, 6, 44)
;
insert into input_rules (output_area_code, relevance, inputC_valid, inputB_invalid) 
values
  ('E', 20, 6, '|55|66|' )
, ('E', 20, 4, '|55|66|' )
;


insert into test 
  (inputA, inputB, inputC) values 
  (1, 56, null)
, (3, 44, null), (3, 66, null)
, (1, 66, null), (1, 88, null)
, (null, 66, 6), (null, 88, 6)

select * from input_rules

id | output_area_code | relevance | inputA_valid | inputB_valid | inputC_valid | inputA_invalid | inputB_invalid | inputC_invalid
-: | :--------------- | --------: | -----------: | -----------: | -----------: | :------------- | :------------- | :-------------
 1 | X                |         0 |         null |         null |         null | null           | null           | null          
 2 | Z                |        10 |            1 |         null |         null | null           | null           | null          
 3 | HQ               |        20 |            1 |         null |         null | null           | |55|56|        | null          
 4 | HQ               |        20 |            2 |         null |         null | null           | |55|56|        | null          
 5 | HQ               |        20 |            3 |         null |         null | null           | |55|56|        | null          
 6 | N                |        30 |            3 |           27 |         null | null           | null           | null          
 7 | N                |        30 |            3 |           44 |         null | null           | null           | null          
 8 | N                |        30 |            6 |           27 |         null | null           | null           | null          
 9 | N                |        30 |            6 |           44 |         null | null           | null           | null          
10 | E                |        20 |         null |         null |            6 | null           | |55|66|        | null          
11 | E                |        20 |         null |         null |            4 | null           | |55|66|        | null          

select *
from test t
outer apply (
  select top 1 ref.name as output
  from input_rules r
  join output_areas ref on ref.code = r.output_area_code
  where (r.inputA_valid is null or r.inputA_valid = t.inputA) 
    and (r.inputB_valid is null or r.inputB_valid = t.inputB) 
    and (r.inputC_valid is null or r.inputC_valid = t.inputC)
    and (r.inputA_invalid is null or r.inputA_invalid not like concat('%|', t.inputA, '|%')) 
    and (r.inputB_invalid is null or r.inputB_invalid not like concat('%|', t.inputB, '|%')) 
    and (r.inputC_invalid is null or r.inputC_invalid not like concat('%|', t.inputC, '|%')) 
  order by r.relevance desc, r.output_area_code asc
) ca

id | inputA | inputB | inputC | output  
-: | -----: | -----: | -----: | :-------
 1 |      1 |     56 |   null | The Zone
 2 |      3 |     44 |   null | Northern
 3 |      3 |     66 |   null | HQ      
 4 |      1 |     66 |   null | HQ      
 5 |      1 |     88 |   null | HQ      
 6 |   null |     66 |      6 | Extern  
 7 |   null |     88 |      6 | Eastern 

db<>fiddle here