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'; 1 2208
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'; This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Carlos Ribeiro |
last post by:
Hello all,
Here I am using some deeply nested, tree-like data structures. In some
situations I need to traverse the tree; the old-style way to do it is
to write a recursive method on the node class, as in:
def walk(self):
"""old-style recursive tree traversal"""
child.do_something
for child in childs:
|
by: Steve Goldman |
last post by:
Hi,
I am trying to come up with a way to develop all n-length permutations of a
given list of values. The short function below seems to work, but I can't
help thinking there's a better way. Not being a computer scientist, I find
recursive functions to be frightening and unnatural. I'd appreciate if
anyone can tell me the pythonic idiom to accomplish this.
Thanks for your help,
|
by: |
last post by:
OK:
Purpose: Using user's input and 3 recursive functions, construct an hour
glass figure. Main can only have user input, loops and function calls.
Recursive function 1 takes input and displays a sequence of spaces;
recursive function 2 uses input to display ascending sequence of digits;
likewise, recursive function 3 uses input to display descending sequence of
digits.
I have not followed the instructions completely regarding the...
|
by: Jon Slaughter |
last post by:
#pragma once
#include <vector>
class empty_class
{
};
template <int _I, int _J, class _element, class _property>
class RDES_T
{
|
by: Jon Slaughter |
last post by:
I've managed to put together a template class that basicaly creates a
recursive tree that lets you easily specify the "base" class of that tree
and and ending notes and lets you stop the recursive process if you want.
The problem now is to make a Type list so I can specify more than one node
at a time to "attach" a class to. I think I will be able to handle this but
I want to run the code by you guys to see there are any major design...
| |
by: Victor |
last post by:
Hello,
I've got a situation in which the number of (valid) recursive calls I
make will cause stack overflow. I can use getrlimit (and setrlimit)
to test (and set) my current stack size. However, it is not as
straightforward to determine the base address for my stack space. The
approach I have taken is to save the address of an automatic variable
in main( ), and assume this is a fairly good indicator of my base
address. Then, I can...
|
by: seberino |
last post by:
I'm a compiler newbie and curious if Python grammar is able to
be parsed by a recursive descent parser or if it requires
a more powerful algorithm.
Chris
|
by: champ1979 |
last post by:
I wrote an algorithm to get all the relatives of a person in a family tree. I'm basically getting all the users from the DB and am doing the recursive logic in code, so that there is only 1 call made to the DB. However, I am trying to do the same thing within a stored procedure in SQL using recursive CTEs (I think the performance might be better) but I'm finding it really tough to craft the CTE. I would really appreciate if someone could...
|
by: Just Another Victim of the Ambient Morality |
last post by:
Is pyparsing really a recursive descent parser? I ask this because
there are grammars it can't parse that my recursive descent parser would
parse, should I have written one. For instance:
from pyparsing import *
grammar = OneOrMore(Word(alphas)) + Literal('end')
grammar.parseString('First Second Third end')
|
by: from.future.import |
last post by:
Hi,
I encountered garbage collection behaviour that I didn't expect when
using a recursive function inside another function: the definition of
the inner function seems to contain a circular reference, which means
it is only collected by the mark-and-sweep collector, not by reference
counting. Here is some code that demonstrates it:
===
def outer():
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |