pl/sql, sql question

From: eric givler <egivler_at_themall.net>
Date: 1997/06/01
Message-ID: <3391f2e5.6907853@news.themall.net>#1/1

I have a database that tracks Underground storage tanks. These tanks store different substances. Several attributes describe the tanks. If the tank has the proper "codes" for a series of attributes then the tank is considered compliant.

The user is constantly changing the codes required to make a tank compliant in a single attribute. Here's an example (which has been extensively SHORTENED due to the number of checks):

A tank has a capacity of 1000 gallons, installed 9/12/94, at facility 4325. The tank# is 1. It has attributes in the tank_attribute table:

Attribute 1: (undergound construction)
Code: A - Fiberglass

Attribute 2: Piping System
Code: B - Cathodically protected steel

Attribute 6: Spill Prevention
Code: Y - Yes

Here's the tricky part:
Attribute 5: Pipe Release
If attribute code 2 is C, then good codes for attribute 5 include: A,B,D,E,F, and J.

Attribute 12: Tank Release
If the capacity is <= 550, then A code of D,E,F,H,I,J,K,O is ok. Or a combination of A&B or A&C
(various other combinations for other tank capacities)

Is there a way to make this entire process table driven and user configurable, and come someone assist with the SQL? Here's something I was thinking of:

Table(s):
tank_compliance_rules - MASTER level rules for compliance per attribute

   Rule_No                           number(6)    not null primary key
   attribute_category_code  varchar2(4)  not null
   Date_Created                   date            not null
   comment_text                   varchar2(128)
   SQL_text                           varchar2(2000)  (should this be

in compliance_checks?)

The sql_text might hold a statement that must be true to make the rule valid, ie. tank.capacity <= 550

Compliance_Rule_Check_Xref - since a single attribute may need to pass multiple checks, this would hold a cross-ref of rules vs checks:

# Rule_No number(6) not null
# Check_No number(6) not null

Compliance_Checks
# Check_no number(6) not null primary key

attribute_category_code varchar2(4) not null

   Code                               varchar2(4) not null

(if sql_text went here we could check for capacity, ie. tank release rule)

Some sample data:
Rule ctgy

   1         5
   2         5

 Rule    check
   1         5
   1        7
   2        5
   2        8

 Check  Att  Code
  5        4     C
  6        4     D
  7        5     A
  8        5     B

You can imagine what the procedures looked like before. Some categories are now up to over 200 combinations! Any idea how I can use the tables above. Suggestions VERY welcome. If you can respond, send to : givler_at_dep.state.pa.us (my work email address), and TANKS, I mean THANKS! Received on Sun Jun 01 1997 - 00:00:00 CDT