recursive sql

[a_valued_employee]

My data model has many tables in many hierarchies.
I believe that by using recursion, I can check for all invalid codes in
the working tables that do not exist in the code tables.
For example, I would like to check the EMPLOYEE table for state_codes
that do not exist in the STATE_CT table where
EMPLOYEE.state_ code=STATE_CT.s tate_code.
Another more complex example is ensuring PROJECT.project _code exists in
PROJECT_CT where EMPLOYEE.employ ee_id = PROJECT.employe e_id and
PROJECT.project _code = PROJECT_CT.proj ect_code.

My plan is to dynamically create a sql statement based on a recursive
query. I am willing to write a .ksh script in conjunction with the sql,
but I'd like to get as far as possible with just sql.
The strong point to this approach is simply adding code tables to a
table and have the script run each month to report invalid codes.

Question: I can get a nice table listing, but I am stumped on the
columns - I would like to have all the join columns in a row to create
a WHERE clause. Run this to see the results.
drop table RITables;
create table RITables
(TABLE_ID INTEGER NOT NULL,
TABNAME VARCHAR(50) NOT NULL,
TABSCHEMA VARCHAR(20) NOT NULL,
APPLICATION VARCHAR(20) NOT NULL
) IN STG_MCR_08_01;

insert into RITables
values
(1, 'COMPANY','SCHE MA','ORG_CHART' ),
(2, 'EMPLOYEE','SCH EMA','ORG_CHART '),
(3, 'STATE_CT','SCH EMA','ORG_CHART '),
(4, 'EMPLOYEETYPE_C T','SCHEMA','OR G_CHART'),
(5, 'PROJECT','SCHE MA','ORG_CHART' ),
(6, 'PROJECT_TYPE_C T','SCHEMA','OR G_CHART');

select * from RITables;

----

drop table RIColumns;
create table RIColumns
(COLUMN_ID INTEGER NOT NULL,
TABLE_ID INTEGER NOT NULL,
COLNAME VARCHAR(50) NOT NULL,
KEYCOLUMN CHAR(1) NOT NULL
) IN STG_MCR_08_01;

insert into RIColumns
values
(100,1,'EMPLOYE E_ID','Y'),
(101,2,'EMPLOYE E_ID','Y'),
(102,2,'STATE_C ODE','N'),
(103,2,'EMPLOYE ETYPE_CODE','N' ),
(104,3,'STATE_C ODE','Y'),
(105,4,'EMPLOYE ETYPE_CODE','Y' ),
(106,5,'EMPLOYE E_ID','Y'),
(107,5,'PROJECT _TYPE_CODE','N' ),
(108,6,'PROJECT _TYPE_CODE','Y' );

select * from RIColumns;

----

drop table RIRelationships ;
create table RIRelationships
(RELATIONSHIP_I D INTEGER NOT NULL,
CHILDTABLE_ID INTEGER NOT NULL,
PARENTTABLE_ID INTEGER ,
PARENTCOLUMN_ID INTEGER NOT NULL,
CHILDCOLUMN_ID INTEGER ,
ENFORCE_RI_FLAG CHAR(1) NOT NULL
) IN STG_MCR_08_01;

insert into RIRelationships
values
(999, 1,null,100,null ,'N'),
(1000, 2,1,100,101,'N' ),
(1001, 3,2,102,104,'Y' ),
(1002, 4,2,103,105,'Y' ),
(1003, 5,2,101,106,'N' ),
(1004, 6,5,107,108,'Y' );

drop view RIRelationships _V ;
create view RIRelationships _V
(RELATIONSHIP_I D,
CHILDTABLE_ID, CHILDTABLE_NAME , CHILDCOLUMN_ID, CHILDCOLUMNNAME ,
PARENTTABLE_ID, PARENTTABLE_NAM E, PARENTCOLUMN_ID ,PARENTCOLUMNNA ME,
ENFORCE_RI_FLAG )
as
select RELATIONSHIP_ID ,
CHAR(CHILDTABLE _ID) AS CHILDTABLE_ID, t2.tabname as CHILDTABLE_NAME ,
char(childcolum n_id) as CHILDCOLUMN_ID, c2.colname as
CHILDCOLUMNNAME ,
CHAR(PARENTTABL E_ID) AS PARENTTABLE_ID, t1.tabname as PARENTTABLE_NAM E,
char(parentcolu mn_id) as PARENTCOLUMN_ID , c1.colname as
PARENTCOLUMNNAM E,
ENFORCE_RI_FLAG
from RIRelationships r, RITables t1, RITables t2, RIColumns c1,
RIColumns c2
where t1.table_id = r.parenttable_i d
and t2.table_id = r.childtable_id
and (c1.column_id = r.parentcolumn_ id and
c1.table_id=r.p arenttable_id)
and (c2.column_id = r.childcolumn_i d and c2.table_id =
r.childtable_id );

select * from RIRelationships _V;

--MAIN SQL
WITH parent (pname, pkey, pcolkey, pcolname, cname,ckey, colkey,
ccolname, lvl, path ,cols,flag ) AS
(SELECT DISTINCT PARENTTABLE_NAM E,parenttable_i d, parentcolumn_id ,
parentcolumnnam e, PARENTTABLE_NAM E,parenttable_i d,parentcolumn_ id,
parentcolumnnam e, 0 ,
varchar(PARENTT ABLE_NAME ,100),
varchar(PARENTT ABLE_NAME || '.' || parentcolumnnam e,500),
ENFORCE_RI_FLAG
FROM RIRelationships _V
WHERE PARENTTABLE_ID = '1'
UNION ALL
SELECT C.PARENTTABLE_N AME, C.parenttable_i d , C.parentcolumn_ id,
C.parentcolumnn ame, C.CHILDTABLE_NA ME,C.childtable _id,C.childcolu mn_id,
C.childcolumnna me, P.lvl + 1 ,
rtrim(P.path) || ',' || C.CHILDTABLE_NA ME,
rtrim(P.cols) || ' = ' || C.CHILDTABLE_NA ME || '.' || C.childcolumnna me
,
ENFORCE_RI_FLAG
FROM RIRelationships _V C
,parent P
WHERE P.ckey = C.parenttable_i d
AND P.lvl + 1 < 6
)
SELECT *
FROM parent
where flag='Y';

[Phil Sherman]

I think you have bigger problems than figuring out how to validate
codes. You have a data model where you will need to be adding new
validation tables every month?? What are you going to do with the data
rows that don't match new tables?

RI constraints are one option to validate codes. Check constraints can
also be used. When the database manager validates the content, data is
always consistent and does not need regular scrubbing to guarantee that
it is valid.

Good design identifies ALL of the data relationships that exist at
design time. Extensive research is needed to locate the relationships
that are "known" but not documented so they can be included in the
design. The best designers also bring their personal knowledge of
business applications to the design table to enhance the design process.

Your approach is more like a dairy barn with a latching door at one end
and a missing wall at the other end. The cows have to be rounded up
every day, a time consuming process that could have been eliminated by
building the last wall before starting to use the barn.

Phil Sherman

a_************* **@hotmail.com wrote:

My data model has many tables in many hierarchies.
I believe that by using recursion, I can check for all invalid codes in
the working tables that do not exist in the code tables.
For example, I would like to check the EMPLOYEE table for state_codes
that do not exist in the STATE_CT table where
EMPLOYEE.state_ code=STATE_CT.s tate_code.
Another more complex example is ensuring PROJECT.project _code exists in
PROJECT_CT where EMPLOYEE.employ ee_id = PROJECT.employe e_id and
PROJECT.project _code = PROJECT_CT.proj ect_code.

My plan is to dynamically create a sql statement based on a recursive
query. I am willing to write a .ksh script in conjunction with the sql,
but I'd like to get as far as possible with just sql.
The strong point to this approach is simply adding code tables to a
table and have the script run each month to report invalid codes.

Question: I can get a nice table listing, but I am stumped on the
columns - I would like to have all the join columns in a row to create
a WHERE clause. Run this to see the results.
drop table RITables;
create table RITables
(TABLE_ID INTEGER NOT NULL,
TABNAME VARCHAR(50) NOT NULL,
TABSCHEMA VARCHAR(20) NOT NULL,
APPLICATION VARCHAR(20) NOT NULL
) IN STG_MCR_08_01;

insert into RITables
values
(1, 'COMPANY','SCHE MA','ORG_CHART' ),
(2, 'EMPLOYEE','SCH EMA','ORG_CHART '),
(3, 'STATE_CT','SCH EMA','ORG_CHART '),
(4, 'EMPLOYEETYPE_C T','SCHEMA','OR G_CHART'),
(5, 'PROJECT','SCHE MA','ORG_CHART' ),
(6, 'PROJECT_TYPE_C T','SCHEMA','OR G_CHART');

select * from RITables;

----

drop table RIColumns;
create table RIColumns
(COLUMN_ID INTEGER NOT NULL,
TABLE_ID INTEGER NOT NULL,
COLNAME VARCHAR(50) NOT NULL,
KEYCOLUMN CHAR(1) NOT NULL
) IN STG_MCR_08_01;

insert into RIColumns
values
(100,1,'EMPLOYE E_ID','Y'),
(101,2,'EMPLOYE E_ID','Y'),
(102,2,'STATE_C ODE','N'),
(103,2,'EMPLOYE ETYPE_CODE','N' ),
(104,3,'STATE_C ODE','Y'),
(105,4,'EMPLOYE ETYPE_CODE','Y' ),
(106,5,'EMPLOYE E_ID','Y'),
(107,5,'PROJECT _TYPE_CODE','N' ),
(108,6,'PROJECT _TYPE_CODE','Y' );

select * from RIColumns;

----

drop table RIRelationships ;
create table RIRelationships
(RELATIONSHIP_I D INTEGER NOT NULL,
CHILDTABLE_ID INTEGER NOT NULL,
PARENTTABLE_ID INTEGER ,
PARENTCOLUMN_ID INTEGER NOT NULL,
CHILDCOLUMN_ID INTEGER ,
ENFORCE_RI_FLAG CHAR(1) NOT NULL
) IN STG_MCR_08_01;

insert into RIRelationships
values
(999, 1,null,100,null ,'N'),
(1000, 2,1,100,101,'N' ),
(1001, 3,2,102,104,'Y' ),
(1002, 4,2,103,105,'Y' ),
(1003, 5,2,101,106,'N' ),
(1004, 6,5,107,108,'Y' );

drop view RIRelationships _V ;
create view RIRelationships _V
(RELATIONSHIP_I D,
CHILDTABLE_ID, CHILDTABLE_NAME , CHILDCOLUMN_ID, CHILDCOLUMNNAME ,
PARENTTABLE_ID, PARENTTABLE_NAM E, PARENTCOLUMN_ID ,PARENTCOLUMNNA ME,
ENFORCE_RI_FLAG )
as
select RELATIONSHIP_ID ,
CHAR(CHILDTABLE _ID) AS CHILDTABLE_ID, t2.tabname as CHILDTABLE_NAME ,
char(childcolum n_id) as CHILDCOLUMN_ID, c2.colname as
CHILDCOLUMNNAME ,
CHAR(PARENTTABL E_ID) AS PARENTTABLE_ID, t1.tabname as PARENTTABLE_NAM E,
char(parentcolu mn_id) as PARENTCOLUMN_ID , c1.colname as
PARENTCOLUMNNAM E,
ENFORCE_RI_FLAG
from RIRelationships r, RITables t1, RITables t2, RIColumns c1,
RIColumns c2
where t1.table_id = r.parenttable_i d
and t2.table_id = r.childtable_id
and (c1.column_id = r.parentcolumn_ id and
c1.table_id=r.p arenttable_id)
and (c2.column_id = r.childcolumn_i d and c2.table_id =
r.childtable_id );

select * from RIRelationships _V;

--MAIN SQL
WITH parent (pname, pkey, pcolkey, pcolname, cname,ckey, colkey,
ccolname, lvl, path ,cols,flag ) AS
(SELECT DISTINCT PARENTTABLE_NAM E,parenttable_i d, parentcolumn_id ,
parentcolumnnam e, PARENTTABLE_NAM E,parenttable_i d,parentcolumn_ id,
parentcolumnnam e, 0 ,
varchar(PARENTT ABLE_NAME ,100),
varchar(PARENTT ABLE_NAME || '.' || parentcolumnnam e,500),
ENFORCE_RI_FLAG
FROM RIRelationships _V
WHERE PARENTTABLE_ID = '1'
UNION ALL
SELECT C.PARENTTABLE_N AME, C.parenttable_i d , C.parentcolumn_ id,
C.parentcolumnn ame, C.CHILDTABLE_NA ME,C.childtable _id,C.childcolu mn_id,
C.childcolumnna me, P.lvl + 1 ,
rtrim(P.path) || ',' || C.CHILDTABLE_NA ME,
rtrim(P.cols) || ' = ' || C.CHILDTABLE_NA ME || '.' || C.childcolumnna me
,
ENFORCE_RI_FLAG
FROM RIRelationships _V C
,parent P
WHERE P.ckey = C.parenttable_i d
AND P.lvl + 1 < 6
)
SELECT *
FROM parent
where flag='Y';