Welcome, Guest. Please Login.
10/25/14 at 18:00:18
News:
Home Help Search Login


Pages: 1
Send Topic Print
Orphan Records (Read 4141 times)
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 758
Gender: male
Orphan Records
08/13/10 at 08:51:11
 
Orphan records are records of data that no longer have connections to other data.  
 
When you delete source data with referential integrity, orphan records will appear in your database.
 
All database has them, and for sure evo's as well.
 
I was talking to another ERP Support line and they give a bunch of sql scripts to find and delete these records that dont have referential integrity.
 
I was wondering if ISTECH Support has some and/or you would like to share your scripts with the group.
 
 embarrassed
 
Kelloggs
Back to top
 
 

Evo ERP - 35 Users
MS Access, SQL, Visual Basic
Alberta, CANADA
Email WWW Kelloggs   IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: Orphan Records
Reply #1 - 08/16/10 at 04:52:50
 
SQL scripts could be developed for this, but since DBA/Evo does not use a relational database the referential integrity needs to be handled by the programmer. SM-J-C is about the biggest clean up agent written into DBA/Evo. It identifies and attempts to fix missing and orphaned record issues in the inventory module.
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 758
Gender: male
Re: Orphan Records
Reply #2 - 08/16/10 at 07:46:55
 
I was under the impression that because there is a common data between some tables that makes DBA/EVO a "relational database"
 
Anyways, here is what I use for Purchase Orders: The Purchase Order Number is the "relation" between the two tables.
 
Please use them with care, and as I suspected, there are Orphan records.
 
'POs WITH NO LINES
SELECT  
BKAPPO.BKAP_PO_NUM,  
BKAPPOL.BKAP_POL_CNTR
FROM BKAPPO LEFT JOIN BKAPPOL ON BKAPPO.BKAP_PO_NUM = BKAPPOL.BKAP_POL_PONM
WHERE (((BKAPPOL.BKAP_POL_PONM) Is Null));
 
SELECT  
BKAPHPO.BKAP_PO_NUM,  
BKAPHPOL.BKAP_POL_CNTR
FROM BKAPHPO LEFT JOIN BKAPHPOL ON BKAPHPO.BKAP_PO_NUM = BKAPHPOL.BKAP_POL_PONM
WHERE (((BKAPHPOL.BKAP_POL_PONM) Is Null));
 
 
'POs LINES WITH NO PO HEADER
SELECT
BKAPPOL.BKAP_POL_PONM,  
BKAPPOL.BKAP_POL_CNTR
FROM BKAPPO RIGHT JOIN BKAPPOL ON BKAPPO.BKAP_PO_NUM = BKAPPOL.BKAP_POL_PONM
WHERE (((BKAPPO.BKAP_PO_NUM) Is Null));
 
 
SELECT
BKAPHPOL.BKAP_POL_PONM,  
BKAPHPOL.BKAP_POL_CNTR
FROM BKAPHPO RIGHT JOIN BKAPHPOL ON BKAPHPO.BKAP_PO_NUM = BKAPHPOL.BKAP_POL_PONM
WHERE (((BKAPHPO.BKAP_PO_NUM) Is Null));
 
 Smiley
 
Kelloggs
Back to top
 
 

Evo ERP - 35 Users
MS Access, SQL, Visual Basic
Alberta, CANADA
Email WWW Kelloggs   IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: Orphan Records
Reply #3 - 08/16/10 at 09:52:32
 
DBA/Evo is a program and Pervasive is the database. DBA/Evo uses the transactional (Btrieve) engine for Pervasive. Since there is no reference or relation between the tables in the database the database is not relational. If the definition of the table included the fact that a record corresponding to a key (a master) must be found in another table for the record to be valid then you could use that relation to keep a sub table from having orphans. Because the tables don't have any relationship with each other the programmer must do something every time he changes a table to make sure that any relationships he has created in his programming are also maintained in the data tables. That seldom happens. DBA/Evo nor Pervasive care that there are 87 SO notes in the notes table for which there are no corresponding SOs. The same with Lot and Serial numbers, SO lines, MTICMSTR/BKICMSTR/BKICLOC records, and the relationship between PO headers and lines. Even the keys are iffy. The serial and lot tables don't care when I have two records with the same Item-Lot/Serial-Location combination.  Huh
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: Orphan Records
Reply #4 - 08/16/10 at 09:57:22
 
Once you have found the orphans, what do you do with the info? Do you recreate the missing pieces? delete the orphans?
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 758
Gender: male
Re: Orphan Records
Reply #5 - 08/16/10 at 10:04:05
 
I see your point.  
 
MySQL per example will not allow you to enter/edit/delete a record on a "child table" without the corresponding on the "master table"
 
Anyways, I am big fan of keeping a "clean house",  Tongue
I like to keep my tables clean
 
 Cheesy
 
Kelloggs
Back to top
 
 

Evo ERP - 35 Users
MS Access, SQL, Visual Basic
Alberta, CANADA
Email WWW Kelloggs   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 758
Gender: male
Re: Orphan Records
Reply #6 - 08/16/10 at 10:13:53
 
Quote from GasGiant on 08/16/10 at 09:57:22:
Once you have found the orphans, what do you do with the info? Do you recreate the missing pieces? delete the orphans?

 
That all depent on the record. More oftem I will delete them
Back to top
 
 

Evo ERP - 35 Users
MS Access, SQL, Visual Basic
Alberta, CANADA
Email WWW Kelloggs   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 758
Gender: male
Re: Orphan Records
Reply #7 - 08/16/10 at 11:52:24
 
A few more, please use them with care
 
'Inventory: These two tables have to have the same records
SELECT  
BKICMSTR.BKIC_PROD_CODE,  
MTICMSTR.MTIC_PROD_CODE
FROM BKICMSTR LEFT JOIN MTICMSTR ON BKICMSTR.BKIC_PROD_CODE = MTICMSTR.MTIC_PROD_CODE
WHERE (((MTICMSTR.MTIC_PROD_CODE) Is Null));
 
SELECT  
MTICMSTR.MTIC_PROD_CODE,  
BKICMSTR.BKIC_PROD_CODE
FROM BKICMSTR RIGHT JOIN MTICMSTR ON BKICMSTR.BKIC_PROD_CODE = MTICMSTR.MTIC_PROD_CODE
WHERE (((BKICMSTR.BKIC_PROD_CODE) Is Null));
 
'Inventory: To check if there is orphan records on the Location table
SELECT  
BKICLOC.BKIC_LOC_PROD,  
BKICMSTR.BKIC_PROD_CODE
FROM BKICLOC LEFT JOIN BKICMSTR ON BKICLOC.BKIC_LOC_PROD = BKICMSTR.BKIC_PROD_CODE
WHERE (((BKICMSTR.BKIC_PROD_CODE) Is Null));
 
'Inventory: This query if there is a diference in qty between the master table and the location table
SELECT  
BKICMSTR.BKIC_PROD_CODE,  
BKICMSTR.BKIC_PROD_UOH,  
BKICLOC.BKIC_LOC_CODE,  
Sum(BKICLOC.BKIC_LOC_UOH) AS LOCS
FROM BKICMSTR LEFT JOIN BKICLOC ON BKICMSTR.BKIC_PROD_CODE = BKICLOC.BKIC_LOC_PROD
GROUP BY BKICMSTR.BKIC_PROD_CODE, BKICMSTR.BKIC_PROD_UOH, BKICLOC.BKIC_LOC_CODE
HAVING (Sum(BKICLOC.BKIC_LOC_UOH) - BKICMSTR.BKIC_PROD_UOH) <>0;
Back to top
 
 

Evo ERP - 35 Users
MS Access, SQL, Visual Basic
Alberta, CANADA
Email WWW Kelloggs   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 758
Gender: male
Re: Orphan Records
Reply #8 - 05/13/11 at 13:04:52
 
No idea how is this posible because the BKIC_PROD_CODE field is a Key but I found some disturbing duplicates
 
select distinct BKIC_PROD_CODE  from BKICMSTR
group by BKIC_PROD_CODE
having count(BKIC_PROD_CODE ) > 1
order by BKIC_PROD_CODE  
 
Run this query if it show records You got Duplicates!!!!
 
  Angry
 
Kelloggs
Back to top
 
 

Evo ERP - 35 Users
MS Access, SQL, Visual Basic
Alberta, CANADA
Email WWW Kelloggs   IP Logged
Lynn_Pantic
Administrator
*****


evolution (n) -
gradual change to a
different form

Posts: 5207
Re: Orphan Records
Reply #9 - 05/13/11 at 18:44:20
 
Just because a field is a key does not mean it is required to be unique.  It just means it is an index and can be used as a lookup.
Back to top
 
 

Lynn Pantic
IS Tech Support
lynn@istechsupport.com
Email   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 758
Gender: male
Re: Orphan Records
Reply #10 - 07/14/11 at 11:17:42
 
that is a major flaw on the system the BKIC_PROD_CODE should be unique
 
 lips sealed
Back to top
 
 

Evo ERP - 35 Users
MS Access, SQL, Visual Basic
Alberta, CANADA
Email WWW Kelloggs   IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: Orphan Records
Reply #11 - 07/15/11 at 05:07:50
 
It would be a major flaw is DBA were supposedly built on a relational database, but it was not, it was built on Btrieve. It is not relational and it is not normalized. Data integrity relies entirely on the programmers. We should pity them  Wink
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
Pages: 1
Send Topic Print