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.state_code.
Another more complex example is ensuring PROJECT.project_code exists in
PROJECT_CT where EMPLOYEE.employee_id = PROJECT.employee_id and
PROJECT.project_code = PROJECT_CT.project_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','SCHEMA','ORG_CHART'),
(2, 'EMPLOYEE','SCHEMA','ORG_CHART'),
(3, 'STATE_CT','SCHEMA','ORG_CHART'),
(4, 'EMPLOYEETYPE_CT','SCHEMA','ORG_CHART'),
(5, 'PROJECT','SCHEMA','ORG_CHART'),
(6, 'PROJECT_TYPE_CT','SCHEMA','ORG_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,'EMPLOYEE_ID','Y'),
(101,2,'EMPLOYEE_ID','Y'),
(102,2,'STATE_CODE','N'),
(103,2,'EMPLOYEETYPE_CODE','N'),
(104,3,'STATE_CODE','Y'),
(105,4,'EMPLOYEETYPE_CODE','Y'),
(106,5,'EMPLOYEE_ID','Y'),
(107,5,'PROJECT_TYPE_CODE','N'),
(108,6,'PROJECT_TYPE_CODE','Y');

select * from RIColumns;

----

drop table RIRelationships;
create table RIRelationships
(RELATIONSHIP_ID 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_ID,
CHILDTABLE_ID, CHILDTABLE_NAME, CHILDCOLUMN_ID, CHILDCOLUMNNAME,
PARENTTABLE_ID, PARENTTABLE_NAME, PARENTCOLUMN_ID,PARENTCOLUMNNAME,
ENFORCE_RI_FLAG )
as
select RELATIONSHIP_ID,
CHAR(CHILDTABLE_ID) AS CHILDTABLE_ID, t2.tabname as CHILDTABLE_NAME,
char(childcolumn_id) as CHILDCOLUMN_ID, c2.colname as
CHILDCOLUMNNAME,
CHAR(PARENTTABLE_ID) AS PARENTTABLE_ID,t1.tabname as PARENTTABLE_NAME,
char(parentcolumn_id) as PARENTCOLUMN_ID, c1.colname as
PARENTCOLUMNNAME,
ENFORCE_RI_FLAG
from RIRelationships r, RITables t1, RITables t2, RIColumns c1,
RIColumns c2
where t1.table_id = r.parenttable_id
and t2.table_id = r.childtable_id
and (c1.column_id = r.parentcolumn_id and
c1.table_id=r.parenttable_id)
and (c2.column_id = r.childcolumn_id 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_NAME,parenttable_id, parentcolumn_id,
parentcolumnname, PARENTTABLE_NAME,parenttable_id,parentcolumn_id,
parentcolumnname, 0 ,
varchar(PARENTTABLE_NAME ,100),
varchar(PARENTTABLE_NAME || '.' || parentcolumnname,500),
ENFORCE_RI_FLAG
FROM RIRelationships_V
WHERE PARENTTABLE_ID = '1'
UNION ALL
SELECT C.PARENTTABLE_NAME, C.parenttable_id , C.parentcolumn_id,
C.parentcolumnname, C.CHILDTABLE_NAME,C.childtable_id,C.childcolumn_id ,
C.childcolumnname, P.lvl + 1 ,
rtrim(P.path) || ',' || C.CHILDTABLE_NAME,
rtrim(P.cols) || ' = ' || C.CHILDTABLE_NAME || '.' || C.childcolumnname
,
ENFORCE_RI_FLAG
FROM RIRelationships_V C
,parent P
WHERE P.ckey = C.parenttable_id
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.state_code.
Another more complex example is ensuring PROJECT.project_code exists in
PROJECT_CT where EMPLOYEE.employee_id = PROJECT.employee_id and
PROJECT.project_code = PROJECT_CT.project_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','SCHEMA','ORG_CHART'),
(2, 'EMPLOYEE','SCHEMA','ORG_CHART'),
(3, 'STATE_CT','SCHEMA','ORG_CHART'),
(4, 'EMPLOYEETYPE_CT','SCHEMA','ORG_CHART'),
(5, 'PROJECT','SCHEMA','ORG_CHART'),
(6, 'PROJECT_TYPE_CT','SCHEMA','ORG_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,'EMPLOYEE_ID','Y'),
(101,2,'EMPLOYEE_ID','Y'),
(102,2,'STATE_CODE','N'),
(103,2,'EMPLOYEETYPE_CODE','N'),
(104,3,'STATE_CODE','Y'),
(105,4,'EMPLOYEETYPE_CODE','Y'),
(106,5,'EMPLOYEE_ID','Y'),
(107,5,'PROJECT_TYPE_CODE','N'),
(108,6,'PROJECT_TYPE_CODE','Y');

select * from RIColumns;

----

drop table RIRelationships;
create table RIRelationships
(RELATIONSHIP_ID 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_ID,
CHILDTABLE_ID, CHILDTABLE_NAME, CHILDCOLUMN_ID, CHILDCOLUMNNAME,
PARENTTABLE_ID, PARENTTABLE_NAME, PARENTCOLUMN_ID,PARENTCOLUMNNAME,
ENFORCE_RI_FLAG )
as
select RELATIONSHIP_ID,
CHAR(CHILDTABLE_ID) AS CHILDTABLE_ID, t2.tabname as CHILDTABLE_NAME,
char(childcolumn_id) as CHILDCOLUMN_ID, c2.colname as
CHILDCOLUMNNAME,
CHAR(PARENTTABLE_ID) AS PARENTTABLE_ID,t1.tabname as PARENTTABLE_NAME,
char(parentcolumn_id) as PARENTCOLUMN_ID, c1.colname as
PARENTCOLUMNNAME,
ENFORCE_RI_FLAG
from RIRelationships r, RITables t1, RITables t2, RIColumns c1,
RIColumns c2
where t1.table_id = r.parenttable_id
and t2.table_id = r.childtable_id
and (c1.column_id = r.parentcolumn_id and
c1.table_id=r.parenttable_id)
and (c2.column_id = r.childcolumn_id 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_NAME,parenttable_id, parentcolumn_id,
parentcolumnname, PARENTTABLE_NAME,parenttable_id,parentcolumn_id,
parentcolumnname, 0 ,
varchar(PARENTTABLE_NAME ,100),
varchar(PARENTTABLE_NAME || '.' || parentcolumnname,500),
ENFORCE_RI_FLAG
FROM RIRelationships_V
WHERE PARENTTABLE_ID = '1'
UNION ALL
SELECT C.PARENTTABLE_NAME, C.parenttable_id , C.parentcolumn_id,
C.parentcolumnname, C.CHILDTABLE_NAME,C.childtable_id,C.childcolumn_id ,
C.childcolumnname, P.lvl + 1 ,
rtrim(P.path) || ',' || C.CHILDTABLE_NAME,
rtrim(P.cols) || ' = ' || C.CHILDTABLE_NAME || '.' || C.childcolumnname
,
ENFORCE_RI_FLAG
FROM RIRelationships_V C
,parent P
WHERE P.ckey = C.parenttable_id
AND P.lvl + 1 < 6
)
SELECT *
FROM parent
where flag='Y';