Re: File Join question

Could you run it once, then do a CPYFRMQRYF?

Thanks,

Mark

Mark D. Walter
Senior Programmer/Analyst
CCX, Inc.
mwalter@xxxxxxxxxx
http://www.ccxinc.com


                                                                                
                                                                      
                    Mlpolutta@xxxxxxx                                           
                                                                      
                    Sent by:                     To:     
midrange-l@xxxxxxxxxxxx (Midrange Systems Technical Discussion)                 
             
                    midrange-l-bounces@xx        cc:                            
                                                                      
                    drange.com                   Subject:     File Join 
question                                                                      
                                                                                
                                                                      
                                                                                
                                                                      
                    05/28/2003 02:20 PM                                         
                                                                      
                    Please respond to                                           
                                                                      
                    Midrange Systems                                            
                                                                      
                    Technical Discussion                                        
                                                                      
                                                                                
                                                                      
                                                                                
                                                                      




Hello folks,

I have a join question.  What I want is to create a permanent (actually for
one day) access path in batch that I can then use interactively.  I want to
join 2 (or more) files together, but select only the fields in the first
file.  I want to select based on any field in any file.  I want to sequence
the results in the original sequence of the first file.  I'm asking you
folks (the "braintrust") if this is possible.

Here is an OPNQRYF that actually does accomplish what I want except for one
thing - it isn't a permanent access path.  Running this interactively is
NOT an option! (grin)

OPNQRYF    FILE((FILE1) (FILE2)) FORMAT(FILE1) +
             QRYSLT('skrclc *ne 0') KEYFLD((File1Field1) +
             (File1Field2) (File1Field3) (File1Field4)) JFLD((File1Field1 +
             File2Field1) (File1Field2 File2Field2) (File1Field3
File2Field3) +
             (File1Field4 File2Field4))

Okay, now the SQL version (only selecting 4 fields for brevity) - no key
sequencing is available, and I can't create an index over the view:

create view test as
select File1.File1Field1, File1.File1Field2, File1.File1Field3,
File1.File1Field4
FROM File1, File2 WHERE File2Field5 = 0 and
File1Field1 = File2Field1 and File1Field2 = File2Field2 and File1Field3 =
File2Field3 and File1Field4 = File2Field4

Am I out of luck?  Have I not explained the problem well enough for you to
tell?

Thanks!
Michael Polutta
Atlanta, GA
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.