Prevent Overlapping In Range Tables
May 19, 2015 Ted Holt
In Joining On Ranges, I demonstrated that range tables are a practical replacement for attribute columns. As a rule, ranges should not overlap. (Perhaps there are exceptions.) Here’s why, and also what to do to prevent overlapping values.
Overlapping ranges cause too many rows of the range table to join to a single row of another table, which in turn causes too many rows in the result set. For example, assume the following range table.
FROMITEMNUMBER THRUITEMNUMBER ITEMCATEGORY 134 134999 1 1341 134199 1 488 488999 2 2 299999 3
Item 134120 would join to the first two rows of the table.
The traditional IBM midrange way to keep the unwanted second row out of the range table would be to include the appropriate logic in the file maintenance program. The problem with that approach is that someone may decide to use something other than the file maintenance program to update the range table. That something could be SQL. It could even be a simple Update Data (UPDDTA) command.
It’s better to make the database itself prevent the inclusion of bad data. Preventing overlapping ranges is a simple matter of one check constraint and two triggers.
The check constraint ensures that the beginning of the range is not after the end of the range.
create table ItemCategories for system name ItemCateg (FromItemNumber for column FromItem char(6), ThruItemNumber for column ThruItem char(6), ItemCategory for column Category char(1), primary key (FromItemNumber), constraint ItemCategoryRange1 check (FromItemNumber <= ThruItemNumber) );
If you try to insert or update a row such that the beginning value is greater than the ending value, the system will rudely refuse to change the table, and will send vulgar message SQL0545:
INSERT, UPDATE, or MERGE not allowed by CHECK constraint. Cause . . . . . : The value being inserted or updated does not meet the criteria of CHECK constraint ITEMCATEGORYRANGE1. The operation is not allowed.
The triggers run before inserts and updates to verify that the new data will not overlap existing data. This is not a difficult verification.
create or replace trigger ItemCategoriesBI before insert on ItemCategories referencing new row as n for each row mode db2row begin declare Counter integer default 0; set Counter = (select count(*) from ItemCategories as cat where n .FromItemNumber <= cat.ThruItemNumber and cat.FromItemNumber <= n .ThruItemNumber); if (Counter > 0) then signal sqlstate '75001' ('Items are already assigned to a category.'); end if; end; create or replace trigger ItemCategoriesBU before update on ItemCategories referencing old row as o new row as n for each row mode db2row begin declare Counter integer default 0; if n.FromItemNumber < o.FromItemNumber or n.ThruItemNumber > o.ThruItemNumber then set Counter = (select count(*) from ItemCategories as cat where cat.FromItemNumber <> o .FromItemNumber and n .FromItemNumber <= cat.ThruItemNumber and cat.FromItemNumber <= n .ThruItemNumber); if (Counter > 0) then signal sqlstate '75002' ('Item numbers are already assigned.'); end if; end if; end;
The triggers count the ranges that overlap the values of the new range. For example, assume these existing rows.
FROMITEMNUMBER THRUITEMNUMBER ITEMCATEGORY 134 134999 1 488 488999 2
Assume I try to insert a range that fits inside the first range.
insert into ItemCategories values ('1341', '134199', 1)
The where clause of the insert trigger evaluates to this:
where '1341' <= '134999' and '134' <= '134199');
The query finds one such row. The trigger uses the SIGNAL command to send an escape message to the caller, which brutishly passes along the information through message SQL0723:
SQL trigger ITEMCATEGORIESBI in SOMELIB failed with SQLCODE -438 SQLSTATE 75001. Cause . . . . . : An error has occurred in a triggered SQL statement in trigger ITEMCATEGORIESBI in schema SOMELIB. The SQLCODE is -438, the SQLSTATE is 75001, and the message is Items are already assigned to a category.
An update may also attempt to create an overlap.
update ItemCategories set row = ('4881', '488199', 5) where FromItemNumber = '134'
The WHERE clause of the update trigger evaluates to the following:
where '488' <> '134' and '4881' <= '488999' and '488' <= '488199')
The first condition prevents the trigger from counting the row that is being updated. The second and third conditions are the same ones used in the insert trigger.
As before, the system heartlessly responds with message SQL0723:
SQL trigger ITEMCATEGORIESBU in SOMELIB failed with SQLCODE -438 SQLSTATE 75002. Cause . . . . . : An error has occurred in a triggered SQL statement in trigger ITEMCATEGORIESBU in schema SOMELIB. The SQLCODE is -438, the SQLSTATE is 75002, and the message is Item numbers are already assigned.
Yes, the system is callous and unforgiving, but better an offended user than a corrupt database, I say.