March 19, 2010 at 9:00 am
I'm not sure if this is the correct term for it, but I am trying to implement what I call a "rules table". The idea is a table used to implement rules that, in procedural language, would usually be implemented by conditional statements. However, those rules change from time to time and I would like a solution that allows me to add the appropriate rules to a table rather than having to edit the code every time the users want to add a new rule or change an existing rule.
I have done this in a number of projects now and used a few different approaches, but I have yet to find one that I am really happy with.
Consider a basic example such as this:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Students]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE Students
CREATE TABLE Students (
student_id int NOT NULL PRIMARY KEY,
full_name varchar(50) NOT NULL,
/** ... additional columns ... */
student_type char(1) NOT NULL
CONSTRAINT valid_student_type
CHECK (student_type IN ('A', 'B', 'C')),
class_rank char(2) NOT NULL
CONSTRAINT valid_class_rank
CHECK (class_rank IN ('FR', 'SO', 'JR', 'SR')),
major char(4) NOT NULL, -- foreign key reference to majors table
campus char(2) NOT NULL -- foreign key reference to campus table
)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lists]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE Lists
CREATE TABLE Lists (
list_id char(5) NOT NULL PRIMARY KEY,
list_name varchar(50) NOT NULL
CONSTRAINT unique_list_name UNIQUE NONCLUSTERED
)
SET NOCOUNT ON
INSERT INTO Students VALUES (1000, 'Shantanu Felix', 'A', 'FR', 'ACCT', 'C1')
INSERT INTO Students VALUES (1001, 'Esmund Ramona', 'A', 'FR', 'ENGR', 'C2')
INSERT INTO Students VALUES (1002, 'Lachesis Arin', 'B', 'FR', 'ENGL', 'C1')
INSERT INTO Students VALUES (1003, 'Dardan Elke', 'B', 'SO', 'ENGL', 'C1')
INSERT INTO Students VALUES (1004, 'Kevin Fion', 'C', 'JR', 'COMP', 'C4')
INSERT INTO Students VALUES (1005, 'Festus Líadáin', 'C', 'SR', 'ACCT', 'C3')
INSERT INTO Lists VALUES ('list1', 'List 1')
INSERT INTO Lists VALUES ('list2', 'List 2')
INSERT INTO Lists VALUES ('list3', 'List 3')
INSERT INTO Lists VALUES ('list4', 'List 4')
INSERT INTO Lists VALUES ('list5', 'List 5')
SET NOCOUNT OFF
I am trying to implement rules for joining Students to Lists that can evaluate conditions like these:
1) list1 includes all students whose student_type is 'A' and either belong to the 'ACCT' major or are NOT on campus 'C2' or 'C3'.
2) list2 includes all students whose student_type is 'B', class_rank is 'FR', and either belong to the 'ACCT' major or are NOT on campus 'C2' or 'C3'.
3) list3 includes all students whose student_type is 'B', class_rank is NOT 'FR', and either belong to the 'ENGL' major or are on campus 'C1', 'C2' or 'C4'.
4) list4 includes all students whose student_type is 'C'.
5) list5 includes all students whose student_type is 'A' and whose major is 'ACCT'.
A first stab might yield a CASE statement in an attempt to model a list of procedural if...then statements:
SELECT student_id, full_name, list_id, list_name
FROM Students, Lists
WHERE list_id = CASE
-- condition 1a
WHEN student_type = 'A' AND (major = 'ACCT' OR campus NOT IN ('C2', 'C3')) THEN 'list1'
-- condition 2a
WHEN student_type = 'B' AND class_rank = 'FR' AND (major = 'ACCT' OR campus NOT IN ('C2', 'C3')) THEN 'list2'
-- condition 3a
WHEN student_type = 'B' AND class_rank <> 'FR' AND (major = 'ENGL' or CAMPUS IN ('C1', 'C2', 'C4')) THEN 'list3'
-- condition 4a
WHEN student_type = 'C' THEN 'list4'
-- condition 5a
WHEN student_type = 'A' AND major = 'ACCT' THEN 'list5'
END
/** RESULT
student_id full_name list_id list_name
----------- -------------------- ------- ----------
1000 Shantanu Felix list1 List 1
1002 Lachesis Arin list2 List 2
1003 Dardan Elke list3 List 3
1004 Kevin Fion list4 List 4
1005 Festus Líadáin list4 List 4
*/
As you can see, the CASE statement in the example has an undesired side-effect such that each student can only be joined to one list. The correct result would show Shantanu Felix as a member of both List 1 and List 5. Also, a CASE statement is a hard-coded solution that would either have to be modified every time a rule is added or changed, or else built using dynamic SQL. :pinch:
Another approach could be to use UNION:
-- condition 1b
SELECT student_id, full_name, list_id, list_name
FROM Students, Lists
WHERE list_id = 'list1'
AND student_type = 'A' AND (major = 'ACCT' OR campus NOT IN ('C2', 'C3'))
UNION
-- condition 2b
SELECT student_id, full_name, list_id, list_name
FROM Students, Lists
WHERE list_id = 'list2'
AND student_type = 'B' AND class_rank = 'FR' AND (major = 'ACCT' OR campus NOT IN ('C2', 'C3'))
UNION
-- condition 3b
SELECT student_id, full_name, list_id, list_name
FROM Students, Lists
WHERE list_id = 'list3'
AND student_type = 'B' AND class_rank <> 'FR' AND (major = 'ENGL' or CAMPUS IN ('C1', 'C2', 'C4'))
UNION
-- condition 4b
SELECT student_id, full_name, list_id, list_name
FROM Students, Lists
WHERE list_id = 'list4'
AND student_type = 'C'
UNION
-- condition 5b
SELECT student_id, full_name, list_id, list_name
FROM Students, Lists
WHERE list_id = 'list5'
AND student_type = 'A' AND major = 'ACCT'
/** RESULT
student_id full_name list_id list_name
----------- -------------------- ------- ----------
1000 Shantanu Felix list1 List 1
1000 Shantanu Felix list5 List 5
1002 Lachesis Arin list2 List 2
1003 Dardan Elke list3 List 3
1004 Kevin Fion list4 List 4
1005 Festus Líadáin list4 List 4
*/
The results are correct, but this is another hard-coded/dynamic SQL solution.
For this example, a rules table like this would work:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ListRules]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE ListRules
CREATE TABLE ListRules (
list_id char(5) REFERENCES Lists ( list_id ),
student_type_pattern varchar(10) NOT NULL,
class_rank_pattern varchar(10) NOT NULL,
major_pattern varchar(10) NOT NULL,
campus_pattern varchar(10) NOT NULL,
PRIMARY KEY NONCLUSTERED (
list_id,
student_type_pattern,
class_rank_pattern,
major_pattern,
campus_pattern
)
)
SET NOCOUNT ON
-- condition 1c
-- WHEN student_type = 'A' AND (major = 'ACCT' OR campus NOT IN ('C2', 'C3')) THEN 'list1'
INSERT INTO ListRules VALUES ('list1', 'A', '%', 'ACCT', '%')
INSERT INTO ListRules VALUES ('list1', 'A', '%', '%', 'C1')
INSERT INTO ListRules VALUES ('list1', 'A', '%', '%', 'C4')
-- condition 2c
-- WHEN student_type = 'B' AND class_rank = 'FR' AND (major = 'ACCT' OR campus NOT IN ('C2', 'C3')) THEN 'list2'
INSERT INTO ListRules VALUES ('list2', 'B', 'FR', 'ACCT', '%')
INSERT INTO ListRules VALUES ('list2', 'B', 'FR', '%', 'C1')
INSERT INTO ListRules VALUES ('list2', 'B', 'FR', '%', 'C4')
-- condition 3c
-- WHEN student_type = 'B' AND class_rank <> 'FR' AND (major = 'ENGL' or CAMPUS IN ('C1', 'C2', 'C4')) THEN 'list3'
INSERT INTO ListRules VALUES ('list3', 'B', 'SO', 'ENGL', '%')
INSERT INTO ListRules VALUES ('list3', 'B', 'SO', '%', 'C1')
INSERT INTO ListRules VALUES ('list3', 'B', 'SO', '%', 'C2')
INSERT INTO ListRules VALUES ('list3', 'B', 'SO', '%', 'C4')
INSERT INTO ListRules VALUES ('list3', 'B', 'JR', 'ENGL', '%')
INSERT INTO ListRules VALUES ('list3', 'B', 'JR', '%', 'C1')
INSERT INTO ListRules VALUES ('list3', 'B', 'JR', '%', 'C2')
INSERT INTO ListRules VALUES ('list3', 'B', 'JR', '%', 'C4')
INSERT INTO ListRules VALUES ('list3', 'B', 'SR', 'ENGL', '%')
INSERT INTO ListRules VALUES ('list3', 'B', 'SR', '%', 'C1')
INSERT INTO ListRules VALUES ('list3', 'B', 'SR', '%', 'C2')
INSERT INTO ListRules VALUES ('list3', 'B', 'SR', '%', 'C4')
-- condition 4c
-- WHEN student_type = 'C' THEN 'list4'
INSERT INTO ListRules VALUES ('list4', 'C', '%', '%', '%')
-- condition 5c
-- WHEN student_type = 'A' AND major = 'ACCT' THEN 'list5'
INSERT INTO ListRules VALUES ('list5', 'A', '%', 'ACCT', '%')
SET NOCOUNT OFF
SELECT DISTINCT Students.student_id, Students.full_name, Lists.list_id, Lists.list_name
FROM Students
INNER JOIN
ListRules
ON Students.student_type LIKE ListRules.student_type_pattern
AND Students.class_rank LIKE ListRules.class_rank_pattern
AND Students.major LIKE ListRules.major_pattern
AND Students.campus LIKE ListRules.campus_pattern
INNER JOIN
Lists
ON Lists.list_id = ListRules.list_id
/** RESULT
student_id full_name list_id list_name
----------- -------------------- ------- ----------
1000 Shantanu Felix list1 List 1
1000 Shantanu Felix list5 List 5
1002 Lachesis Arin list2 List 2
1003 Dardan Elke list3 List 3
1004 Kevin Fion list4 List 4
1005 Festus Líadáin list4 List 4
*/
The result is correct, and this option has managed to avoid hard-coding and/or dynamic SQL, which are a good things. However, I have some concerns:
1) This solution includes an index scan on ListRules, which is effectively a table scan since the PK includes every column. Is that acceptable since the rules table should have a small number of rows, especially compared to the other tables?
2) Implementing OR or IN requires materializing a separate line for each valid combination, which can make this table grow pretty quickly. In the example, condition 3c could have been replaced with something using character classes such as '[SJ][OR]' and 'C[124]', but in real data it often won't be that simple.
3) Implementing NOT or NOT IN is even more problematic. It requires knowing the list of possible values for the column (in this case C1, C2, C3, and C4) so you can invert the logic of the rules from "<> 'FR'" to "IN ('SO', 'JR', 'SR')" and from "NOT IN ('C2', 'C3')" to "IN ('C1', 'C4')". This raises the same issue as #2 above. Also, if a class rank of 'GR' or a campus of 'C5' is later added to the Students table, it requires somehow knowing to change the condition again, to "IN ('SO', 'JR', 'SR', 'GR')" or "IN ('C1', 'C4', 'C5')".
So... Is there a better approach to this problem?
Andrew
--Andrew
March 25, 2010 at 9:14 am
it sounds like you are pre-populating the rules(the possible conditions) into a table by a CROSS-JOIN like method. This could go huge if the base tables (your data tables and your rules) are big.
I am no BI people, but I think, those BI tools, e.g. Business Objects, could do what you want to achieve. with base tables( fact tables, dimension tables..etc), they could manipulate the ways to present data according to different criteria relatively easily.
March 29, 2010 at 9:16 am
This is for a front-end web application, not really for reporting. Generally, while the intermediate result involves what looks like a wide cross join, I really just need to find a list of items from which an individual student can choose based at least in part on one or more attributes in the student record. The example I posted used "Lists", but really those items could be anything, such as rooms in a residence hall, departments to contact, etc.
Since posting the question, I have come up with something that looks like it will work. The ListRules table I built looks like this:
/**
* Students belong to the various "lists" based on student_type,
* class_rank, major, and/or campus.
*
* Set up a table of rules that define the conditions to be applied to
* individual columns in the Students table as it should relate to the
* Lists table. Each condition column supports a fixed number of
* operations (=, <>, LIKE, NOT LIKE, IN, NOT IN). Formatting for each
* operation is fixed. Additional operations could be added as needed.
* They just don't make sense on the project that prompted this question.
* Ideally, I wanted to allow conditions longer than 128 characters
* (specifically for IN/NOT IN) but I also wanted to include the
* conditions in a unique key to prevent duplicates, and I ran into
* the 900 byte index limit in my real table. Since my longest condition
* is currently 31 characters (4 values), that's good enough for now.
*/
CREATE TABLE ListRules (
list_id char(5) REFERENCES Lists ( list_id ),
student_type_condition varchar(128) DEFAULT 'LIKE ''%''' NOT NULL
CHECK (
-- define the supported syntax for student_type_condition
student_type_condition LIKE 'LIKE ''%'''
OR student_type_condition LIKE 'NOT LIKE ''%'''
OR student_type_condition LIKE '= ''_'''
OR student_type_condition LIKE '<> ''_'''
OR student_type_condition LIKE 'IN (%)'
OR student_type_condition LIKE 'NOT IN (%)'
),
class_rank_condition varchar(128) DEFAULT 'LIKE ''%''' NOT NULL,
CHECK (
-- define the supported syntax for class_rank_condition
class_rank_condition LIKE 'LIKE ''%'''
OR class_rank_condition LIKE 'NOT LIKE ''%'''
OR class_rank_condition LIKE '= ''__'''
OR class_rank_condition LIKE '<> ''__'''
OR class_rank_condition LIKE 'IN (%)'
OR class_rank_condition LIKE 'NOT IN (%)'
),
major_condition varchar(128) DEFAULT 'LIKE ''%''' NOT NULL,
CHECK (
-- define the supported syntax for major_condition
major_condition LIKE 'LIKE ''%'''
OR major_condition LIKE 'NOT LIKE ''%'''
OR major_condition LIKE '= ''____'''
OR major_condition LIKE '<> ''____'''
OR major_condition LIKE 'IN (%)'
OR major_condition LIKE 'NOT IN (%)'
),
campus_condition varchar(128) DEFAULT 'LIKE ''%''' NOT NULL,
CHECK (
-- define the supported syntax for campus_condition
campus_condition LIKE 'LIKE ''%'''
OR campus_condition LIKE 'NOT LIKE ''%'''
OR campus_condition LIKE '= ''__'''
OR campus_condition LIKE '<> ''__'''
OR campus_condition LIKE 'IN (%)'
OR campus_condition LIKE 'NOT IN (%)'
),
PRIMARY KEY NONCLUSTERED (
list_id,
student_type_condition,
class_rank_condition,
major_condition,
campus_condition
)
)
-- condition 1c
-- WHEN student_type = 'A' AND (major = 'ACCT' OR campus NOT IN ('C2', 'C3')) THEN 'list1'
INSERT INTO ListRules (list_id, student_type_condition, class_rank_condition, major_condition, campus_condition)
SELECT 'list1', '= ''A''', 'LIKE ''%''', '= ''ACCT''', 'LIKE ''%''' UNION ALL
SELECT 'list1', '= ''A''', 'LIKE ''%''', 'LIKE ''%''', 'NOT IN (''C2'', ''C3'')' UNION ALL
-- condition 2c
-- WHEN student_type = 'B' AND class_rank = 'FR' AND (major = 'ACCT' OR campus NOT IN ('C2', 'C3')) THEN 'list2'
SELECT 'list2', '= ''B''', '= ''FR''', '= ''ACCT''', 'LIKE ''%''' UNION ALL
SELECT 'list2', '= ''B''', '= ''FR''', 'LIKE ''%''', 'NOT IN (''C2'', ''C3'')' UNION ALL
-- condition 3c
-- WHEN student_type = 'B' AND class_rank <> 'FR' AND (major = 'ENGL' or CAMPUS IN ('C1', 'C2', 'C4')) THEN 'list3'
SELECT 'list3', '= ''B''', '<> ''FR''', '= ''ENGL''', 'LIKE ''%''' UNION ALL
SELECT 'list3', '= ''B''', '<> ''FR''', 'LIKE ''%''', 'IN (''C1'', ''C2'', ''C4'')' UNION ALL
-- condition 4c
-- WHEN student_type = 'C' THEN 'list4'
SELECT 'list4', '= ''C''', 'LIKE ''%''', 'LIKE ''%''', 'LIKE ''%''' UNION ALL
-- condition 5c
-- WHEN student_type = 'A' AND major = 'ACCT' THEN 'list5'
SELECT 'list5', '= ''A''', 'LIKE ''%''', '= ''ACCT''', 'LIKE ''%'''
Then I created two tables for each rule: a Conditions table and a Population table, similar to these:
/**
* The %Population table is used to materialize a list of values
* matching an IN/NOT IN condition described in one of those columns.
* The %Conditions table is a map from the potentially long text value
* describing the condition to an integer value. This helps to keep the
* %Population tables smaller.
*/
/**
* A table to map the conditions stored in ListRules.student_type_condition
* to a unique integer key
*/
CREATE TABLE StudentTypeConditions (
student_type_condition varchar(128) NOT NULL PRIMARY KEY,
student_type_key int IDENTITY(1, 1) NOT NULL UNIQUE
)
/**
* A table that maps a specific condition in StudentTypeConditions to all values
* of student_type that match the condition.
*/
CREATE TABLE StudentTypePopulation (
student_type_key int NOT NULL REFERENCES StudentTypeConditions ( student_type_key ) ON DELETE CASCADE,
student_type char(1) NOT NULL REFERENCES StudentTypes ( student_type ) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY ( student_type_key, student_type )
)
The process to maintain these tables does involve dynamic SQL, but it would only need to be used during the nightly data refresh. It looks something like this:
/**********************************************************************
* This is the logic that will be in a stored procedure used to update
* all of the %Conditions and %Population tables based on the
* conditions stored in ListRules
**********************************************************************/
-- A variable to hold a dynamic SQL statement to be executed.
DECLARE @stmt nvarchar(4000)
/**
* Student Types
*/
-- Delete existing populations
DELETE dbo.StudentTypePopulation
-- Delete any conditions that no longer exist in ListRules
DELETE dbo.StudentTypeConditions
WHERE NOT EXISTS (
SELECT *
FROM dbo.ListRules AS Rules
WHERE (
/**
* Limit to IN and NOT IN. This could be expanded
* to include other conditions if desired, but
* these are the only conditions that cannot be
* easily expressed at runtime without dynamic SQL.
*/
Rules.student_type_condition LIKE 'IN (%'
OR Rules.student_type_condition LIKE 'NOT IN (%'
)
AND StudentTypeConditions.student_type_condition = RTRIM(LTRIM(SUBSTRING(Rules.student_type_condition, PATINDEX('%IN (%', Rules.student_type_condition), LEN(Rules.student_type_condition) + 1)))
)
/**
* Insert any new conditions into StudentTypeConditions.
* Trim the condition and strip off the initial NOT to keep the number
* of unique populations as small as possible. The conditions
* IN ('A', 'B', 'C') and NOT IN ('A', 'B', 'C') are both operating on
* the set { A, B, C }
*/
INSERT INTO dbo.StudentTypeConditions (student_type_condition)
SELECT DISTINCT SUBSTRING(student_type_condition, PATINDEX('%IN (%', student_type_condition), LEN(student_type_condition) + 1)
FROM dbo.ListRules
WHERE NOT EXISTS (
SELECT *
FROM dbo.StudentTypeConditions AS Rules
WHERE Rules.student_type_condition = SUBSTRING(ListRules.student_type_condition, PATINDEX('%IN (%', ListRules.student_type_condition), LEN(ListRules.student_type_condition) + 1)
)
AND (
/**
* Limit to IN and NOT IN. This could be expanded
* to include other conditions if desired, but
* these are the only conditions that cannot be
* easily expressed at runtime without dynamic SQL.
*/
student_type_condition LIKE 'IN (%'
OR student_type_condition LIKE 'NOT IN (%'
)
-- Initialize @stmt
SET @stmt = ''
-- Build @stmt from the list of unique conditions in StudentTypeConditions
SELECT @stmt = @stmt
+ N'INSERT INTO StudentTypePopulation (student_type_key, student_type) SELECT '
+ CONVERT(nvarchar(10), student_type_key)
+ N', student_type FROM StudentTypes WHERE student_type '
+ student_type_condition
+ N'; ' + NCHAR(10)
FROM dbo.StudentTypeConditions
-- Execute the dynamic SQL
EXEC sp_executesql @stmt
Once this is built, the final query looks like this:
SELECT DISTINCT Students.student_id, Students.full_name, Lists.list_id, Lists.list_name
FROM Students, Lists, ListRules
WHERE Lists.list_id = ListRules.list_id
/**
* Evaluate each of the conditions in ListRules against the
* corresponding values in Students. In spite of the ORs used,
* each condition is mutually exclusive within the group so that
* the entire block functions like a CASE statement. (I tried a
* using a CASE statement as well, and saw no difference in
* execution.)
*/
AND (
-- If the condition is LIKE '%' it will return the full set;
-- so don't bother testing each row.
(student_type_condition = 'LIKE ''%''')
-- Test for equality
OR (student_type_condition LIKE '= ''_''' AND student_type = SUBSTRING(student_type_condition, 4, 1))
-- Test for inequality
OR (student_type_condition LIKE '<> ''_''' AND student_type <> SUBSTRING(student_type_condition, 5, 1))
-- Test for rows matching a pattern
OR (student_type_condition <> 'LIKE ''%''' AND student_type_condition LIKE 'LIKE ''%''' AND student_type LIKE SUBSTRING(student_type_condition, 7, LEN(student_type_condition) - 7))
-- Test for rows that do not match a pattern
OR (student_type_condition LIKE 'NOT LIKE ''%''' AND student_type NOT LIKE SUBSTRING(student_type_condition, 7, LEN(student_type_condition) - 7))
-- Test for student types in a set
OR (student_type_condition LIKE 'IN (%)' AND EXISTS (
SELECT *
FROM StudentTypeConditions AS STC
INNER JOIN
StudentTypePopulation AS STP
ON STP.student_type_key = STC.student_type_key
WHERE ListRules.student_type_condition = STC.student_type_condition
AND Students.student_type = STP.student_type
))
-- Test for student types not in a set
OR (student_type_condition LIKE 'NOT IN (%)' AND NOT EXISTS (
SELECT *
FROM StudentTypeConditions AS STC
INNER JOIN
StudentTypePopulation AS STP
ON STP.student_type_key = STC.student_type_key
WHERE ListRules.student_type_condition = 'NOT ' + STC.student_type_condition
AND Students.student_type = STP.student_type
))
)
AND (
(major_condition = 'LIKE ''%''')
OR (major_condition LIKE '= ''____''' AND major = SUBSTRING(major_condition, 4, 4))
OR (major_condition LIKE '<> ''____''' AND major <> SUBSTRING(major_condition, 5, 4))
OR (major_condition <> 'LIKE ''%''' AND major_condition LIKE 'LIKE ''%''' AND major LIKE SUBSTRING(major_condition, 7, LEN(major_condition) - 7))
OR (major_condition LIKE 'NOT LIKE ''%''' AND major NOT LIKE SUBSTRING(major_condition, 7, LEN(major_condition) - 7))
OR (major_condition LIKE 'IN (%)' AND EXISTS (
SELECT *
FROM MajorConditions AS STC
INNER JOIN
MajorPopulation AS STP
ON STP.major_key = STC.major_key
WHERE ListRules.major_condition = STC.major_condition
AND Students.major = STP.major
))
OR (major_condition LIKE 'NOT IN (%)' AND NOT EXISTS (
SELECT *
FROM MajorConditions AS STC
INNER JOIN
MajorPopulation AS STP
ON STP.major_key = STC.major_key
WHERE ListRules.major_condition = 'NOT ' + STC.major_condition
AND Students.major = STP.major
))
)
AND (
(campus_condition = 'LIKE ''%''')
OR (campus_condition LIKE '= ''__''' AND campus = SUBSTRING(campus_condition, 4, 2))
OR (campus_condition LIKE '<> ''__''' AND campus <> SUBSTRING(campus_condition, 5, 2))
OR (campus_condition <> 'LIKE ''%''' AND campus_condition LIKE 'LIKE ''%''' AND campus LIKE SUBSTRING(campus_condition, 7, LEN(campus_condition) - 7))
OR (campus_condition LIKE 'NOT LIKE ''%''' AND campus NOT LIKE SUBSTRING(campus_condition, 7, LEN(campus_condition) - 7))
OR (campus_condition LIKE 'IN (%)' AND EXISTS (
SELECT *
FROM CampusConditions AS STC
INNER JOIN
CampusPopulation AS STP
ON STP.campus_key = STC.campus_key
WHERE ListRules.campus_condition = STC.campus_condition
AND Students.campus = STP.campus
))
OR (campus_condition LIKE 'NOT IN (%)' AND NOT EXISTS (
SELECT *
FROM CampusConditions AS STC
INNER JOIN
CampusPopulation AS STP
ON STP.campus_key = STC.campus_key
WHERE ListRules.campus_condition = 'NOT ' + STC.campus_condition
AND Students.campus = STP.campus
))
)
AND (
(class_rank_condition = 'LIKE ''%''')
OR (class_rank_condition LIKE '= ''__''' AND class_rank = SUBSTRING(class_rank_condition, 4, 2))
OR (class_rank_condition LIKE '<> ''__''' AND class_rank <> SUBSTRING(class_rank_condition, 5, 2))
OR (class_rank_condition <> 'LIKE ''%''' AND class_rank_condition LIKE 'LIKE ''%''' AND class_rank LIKE SUBSTRING(class_rank_condition, 7, LEN(class_rank_condition) - 7))
OR (class_rank_condition LIKE 'NOT LIKE ''%''' AND class_rank NOT LIKE SUBSTRING(class_rank_condition, 7, LEN(class_rank_condition) - 7))
OR (class_rank_condition LIKE 'IN (%)' AND EXISTS (
SELECT *
FROM ClassRankConditions AS STC
INNER JOIN
ClassRankPopulation AS STP
ON STP.class_rank_key = STC.class_rank_key
WHERE ListRules.class_rank_condition = STC.class_rank_condition
AND Students.class_rank = STP.class_rank
))
OR (class_rank_condition LIKE 'NOT IN (%)' AND NOT EXISTS (
SELECT *
FROM ClassRankConditions AS STC
INNER JOIN
ClassRankPopulation AS STP
ON STP.class_rank_key = STC.class_rank_key
WHERE ListRules.class_rank_condition = 'NOT ' + STC.class_rank_condition
AND Students.class_rank = STP.class_rank
))
)
In production, this query would be further restricted by an additional condition in the WHERE clause to filter the results to those where student_id matches an input parameter.
I tested this theory with a copy of the data that will be in the live system, and it appears to perform pretty well so far. I think I like this solution better than some that we have used in previous projects, but I would still be interested in feedback from other people to see if someone has a better way to tackle the problem.
I also attached a full SQL script with all the tables to test the concept.
Andrew
--Andrew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply