Re: Referencial Integrity - Was: RPG Cycle

[Raul Jager <raul@xxxxxxxxxx>]

To clean up the files you can use SQL:

Delete from DETAIL where KEY not in (Select KEY from MASTER)

It may be a good idea to make a record of the orphans before deleting:

Insert into GARBISH select * from DETAIL where KEY not in (select KEY
from MASTER)

In these samples I'm calling DETAIL your detail file, MASTER the master
file and KEY the field that you use as key to relate both.

________________________________________________________________________________
James W. Kilgore wrote:
> 
> That brings back my original question, how to you clean the files?
> 
> Do you write a cycle program using NMR to delete orphans?
> 
> Raul Jager wrote:
> 
> > <<snip>>
> >
> > Once your files are clean, RI will prevent to delete a master record
> > unlees you delete the detail firs, it will also prevente a detail record
> > to be written if there is no master.  RI will also prevent to update the
> > keys to a value that will leave orphans.
> >
> 
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to RPG400-L@midrange.com.
> | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---

-- 
  Raul A. Jager W.
Asuncion - Paraguay
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---