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:
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. |