Deutsche VersionFill and query a decision table with defaults and exceptions. This article is based on a post in OTN:
Why
In the course of time a piece of code that was used to take decisions programmatically grew into a mess of multiple nested IF statements. We decided to implement it as a decision table.
How
Because there are more than 20,000 possible combinations of the attributes, a table that holds each of them is out of question, nobody would be able to maintain it.
Here is a simplified example with clients which each can be in different regions, one attribute that can be Yes or No and one attribute that is derived from many other properties and there is no table that has every possible value of this attribute (I know there should be a table for this instead of hard coding them but that is another story).
The decision table is hierarchical meaning that each column carries more weight than the columns to the right.
The result is either Y (YES) or N (NO)
Script and Data
DROP VIEW ranked_decisions;
DROP TABLE decisions;
DROP TABLE clients;
DROP TABLE regions;
CREATE TABLE clients
(
client_id INTEGER NOT NULL
,client_name VARCHAR2 (100)
) ;
ALTER TABLE clients ADD CONSTRAINT cli_pk PRIMARY KEY ( client_id ) ;
CREATE TABLE regions
(
region_id INTEGER NOT NULL
,region_name VARCHAR2 (100)
) ;
ALTER TABLE regions ADD CONSTRAINT reg_pk PRIMARY KEY ( region_id ) ;
CREATE TABLE decisions
(
client_id INTEGER NOT NULL
,region_id INTEGER
,dec_yn_1 VARCHAR2 (1 CHAR)
,dec_attr_1 VARCHAR2 (10 CHAR)
,dec_result VARCHAR2 (1 CHAR)
) ;
ALTER TABLE decisions ADD CONSTRAINT dec_cli_fk FOREIGN KEY ( client_id ) REFERENCES clients ( client_id ) ;
ALTER TABLE decisions ADD CONSTRAINT dec_reg_fk FOREIGN KEY ( region_id ) REFERENCES regions ( region_id ) ;
INSERT INTO clients (client_id,client_name) VALUES (1,'A');
INSERT INTO clients (client_id,client_name) VALUES (2,'B');
INSERT INTO clients (client_id,client_name) VALUES (3,'C');
INSERT INTO clients (client_id,client_name) VALUES (4,'D');
INSERT INTO clients (client_id,client_name) VALUES (5,'E');
INSERT INTO regions (region_id,region_name) VALUES (10,'BY');
INSERT INTO regions (region_id,region_name) VALUES (11,'BW');
INSERT INTO regions (region_id,region_name) VALUES (12,'HE');
INSERT INTO regions (region_id,region_name) VALUES (13,'TH');
INSERT INTO regions (region_id,region_name) VALUES (14,'HH');
--Default for each client
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 1, NULL, NULL, NULL, 'N');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 2, NULL, NULL, NULL, 'N');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 3, NULL, NULL, NULL, 'Y');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 4, NULL, NULL, NULL, 'N');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 5, NULL, NULL, NULL, 'Y');
The Query
CREATE OR REPLACE VIEW ranked_decisions AS
WITH all_combinations AS (
--generate each possible combination of the attributes
SELECT cli.client_id
,reg.region_id
,yn.yn_1
,attr.attr_1
,dc.dec_result
-- Ranking of the results
-- If there is a result with dc.client_id <> NULL, a value of 8 is associated
-- Accordingly for dc.region_id 4, dc.dec_yn_1 2 and for dc.dec_attr_1 1
-- Thus I can be sure that combinations that have a match get a higher
-- value than generated combinations
,ROW_NUMBER () OVER (
PARTITION BY cli.client_id,reg.region_id,yn.yn_1,attr.attr_1
ORDER BY CASE WHEN dc.client_id IS NULL THEN 0 ELSE 8 END
+ CASE WHEN dc.region_id IS NULL THEN 0 ELSE 4 END
+ CASE WHEN dc.dec_yn_1 IS NULL THEN 0 ELSE 2 END
+ CASE WHEN dc.dec_attr_1 IS NULL THEN 0 ELSE 1 END
DESC
) r
FROM clients cli
CROSS JOIN regions reg
CROSS JOIN (
SELECT 'Y' yn_1 FROM dual UNION ALL
SELECT 'N' yn_1 FROM dual
) yn
CROSS JOIN (
SELECT 'LA' attr_1 FROM dual UNION ALL
SELECT 'M' attr_1 FROM dual UNION ALL
SELECT 'SR' attr_1 FROM dual UNION ALL
SELECT 'N' attr_1 FROM dual
) attr
JOIN decisions dc
ON ( (cli.client_id = dc.client_id OR dc.client_id IS NULL)
AND (reg.region_id = dc.region_id OR dc.region_id IS NULL)
AND (yn.yn_1 = dc.dec_yn_1 OR dc.dec_yn_1 IS NULL)
AND (attr.attr_1 = dc.dec_attr_1 OR dc.dec_attr_1 IS NULL)
)
)
SELECT ac.client_id
,ac.region_id
,ac.yn_1
,ac.attr_1
,ac.dec_result
FROM all_combinations ac
WHERE r = 1;
SELECT dec_result
FROM ranked_decisions
WHERE client_id = 4
AND region_id = 14
AND yn_1 = 'N'
AND attr_1 = 'LA';
Of course we get a 'N'.
The Exceptions
Let's add some exceptions
--Default for region HH is Y for client A and D
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 1, 14, NULL, NULL, 'Y');
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 4, 14, NULL, NULL, 'Y');
Now we get an 'Y' for the same query
Other exceptions might be
-- Client A is Y if region is BY
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 1, 10, NULL, NULL, 'Y');
--except if attribute 1 is LA
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 1, 10, NULL, 'LA', 'N');
-- Client D is Y if YN-attribute is Y
INSERT INTO decisions (client_id,region_id,dec_yn_1,dec_attr_1,dec_result)
VALUES ( 4, NULL, 'Y', NULL, 'Y');
Our decision Table has the following content
CLIENT_ID REGION_ID D DEC_ATTR_1 D
----------- ----------- - ---------- -
1 N
2 N
3 Y
4 N
5 Y
1 14 Y
4 14 Y
1 10 Y
1 10 LA N
4 Y Y
Impressum