ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> General DBA Topics >> Orphan Records
http://www.istechforum.com/YaBB.pl?num=1281714671

Message started by Kelloggs on 08/13/10 at 08:51:11

Title: Orphan Records
Post by Kelloggs on 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.

:-[

Kelloggs

Title: Re: Orphan Records
Post by GasGiant on 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.

Title: Re: Orphan Records
Post by Kelloggs on 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));

:)

Kelloggs

Title: Re: Orphan Records
Post by GasGiant on 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.  :-?

Title: Re: Orphan Records
Post by 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?

Title: Re: Orphan Records
Post by Kelloggs on 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",  :P
I like to keep my tables clean

:D

Kelloggs

Title: Re: Orphan Records
Post by Kelloggs on 08/16/10 at 10:13:53


GasGiant wrote:
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

Title: Re: Orphan Records
Post by Kelloggs on 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;

Title: Re: Orphan Records
Post by Kelloggs on 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!!!!

 >:(

Kelloggs

Title: Re: Orphan Records
Post by Lynn_Pantic on 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.

Title: Re: Orphan Records
Post by Kelloggs on 07/14/11 at 11:17:42

that is a major flaw on the system the BKIC_PROD_CODE should be unique

:-X

Title: Re: Orphan Records
Post by GasGiant on 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  ;)

ISTech Support Forum » Powered by YaBB 2.1!
YaBB © 2000-2005. All Rights Reserved.