RE: Secondary Keys

[Contractor1@xxxxxxxxxxxxxxxxx]

Yeah, I know. The subfile module does allow deleting of records, but I use
the physical file to do that no matter what file was used to build the
subfile. For updating records the key values are grabbed from the subfile
record and passed to a single record module that is also built over the
physical file.

Should we be asking for an enhancement to the database that will allow
fields in the secondary files to be used as key values. Or is this not
technically possible?

And again this database is not designed correctly. I would have never
reached this problem if I was allowed to make some structural changes, but
that would 'take too long' and 'cost to much' and is not within the scope
of this project. Please, no replies about how this company could save money
in the long run with a better structured database. I know this and I have
already had many discussions with the project lead. Of course, I might not
be here if they did, hence the saving money part. :)

As for now I'm going with the join file. Not allowing a record to be added
to File1 if a matching key is not in File2. A clean up program to be added
to the weekly clean-up programs to delete any orphan records in File1 when
records from File2 are deleted. File2 keys are hardly ever updated, pretty
much only deleted and created.

Thanks for everyone's input. You've been a great help. Thanks to Diane for
the SQLRPG examples. We are thinking about applying those to another task.

Patrick Conner
www.ConnecTown.com
(828) 244-0822



                                                                                
                    
                    Buck Calabro                                                
                    
                    <buck.calabro@aptissof        To:     rpg400-l@midrange.com 
                    
                    tware.com>                    cc:                           
                    
                    Sent by:                      Subject:     RE: Secondary 
Keys                   
                    owner-rpg400-l@midrang                                      
                    
                    e.com                                                       
                    
                                                                                
                    
                                                                                
                    
                    12/07/00 11:50 AM                                           
                    
                    Please respond to                                           
                    
                    RPG400-L                                                    
                    
                                                                                
                    
                                                                                
                    




OPNQRYF or SQL will do what you require.  Be aware that joins (in any form)
will not allow updating - you'll need to use another access path to perform
the record maintenance.  Performance issues are addressed by creating
logical files with the proper keys.  Both OPNQRYF and SQL will use existing
access paths if they are keyed the way your "query" wants the records.

Buck Calabro



+---
| 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
+---