|
James, Thanks for your help. I am sorry , I could'nt able to understand your idea. Could you please expain me in more specific. On your mail, you have mentioned that you are using OPNQRYF on your RPG to generate the report. In our case, we already have the OPNQRYF on the CL, which creates a PF , which is used to generate the report on our RPG. We actually want to elliminate this OPNQRYF because it builds the access path every time the user selects an option, which also takes a long time. In our CL, we have 19 different OPNQRYF command which uses different Key flds, some of key flds from Primary file, some of the flds from secodary files. the key flds are all mixed. Since I can'nt use the key flds from Secondary file, I am struck. Now is there a way that I can use *NONE in the LF ( where secondary file key flds are used ), and handle in RPG?. Thanks again Regards Raj From: email@james-w-kilgore.com (James W Kilgore) AT Internet on 12-18-98 10:50 PM To: MIDRANGE-L@midrange.com AT Internet@ccmail cc: (bcc: Rajan Srinivasan/Servicing/SuperiorBank) Subject: Re: Join Logical File Problem Rajan, Think about this, 19 views to "maybe" up to 3 files. You can create logical files with *none (?) as place holders for missing information in the secondary files and hard code the logical selections within your report request program. We faced a similar problem doing sales analysis. Some requests were by sales person, or customer. or item. etc. and then some requests were for product class within territory or item within customer within sales rep, etc. and have about 19 ways to view the data by one way, by two ways was 19 * 18 ways and three ways was 19* 18* 16 ways. That's a LOT of logicals! At first we started hard coding the known requests just to get the job done. Once we had a breather, we created a few physical files (1 view, 2 views, 3 views, 4 views) just to have a OPNQRYF FORMAT definition. Now when a user makes a request, (one of thousands of mathematical possibilities) they are all covered through a call to a single RPG program that builds the JOIN/MAPFLD/QRYSLT strings that the CL caller passes to an QCMDEXC and the resulting OPNQRYF optimizes to existing logicals and requests are served within a respectable time frame. The trick, make the RPG program capable of handling four deep/ninteen variables (in any combination) without illogical structure and pass back either the requirements of an OPNQRYF or SQL structure to satisfy the user request. When you get really creative, you can serve 19 entities to "N" levels (combinations) within a single request constructor and go home at the end of the day feeling a true sense of achievement! :-) Rajan.Srinivasan@allfun.com wrote: > Hello All, > We are trying to replace an Open Query > File with Join Logical File and got struck. Any help is > greatly Appriciated. > > IN BRIEF : JOIN LOGICAL FILE - Not allowing to have Key field > from Secondary File fields. > > We are using the Open Query File. We are trying to take out the > Open Query file, ( which generates the acceess path > every time, which takes long time ), and use Join Logical file with > the relavent Key list. > > IN DETAIL. : > > We are showing a selection screen , of which the user can select 19 > different selection creteria to > print the report in 19 different ways. It is being called and > executed from a CL to RPG. > > The data is being taken from 3 Physical files. > > On CL, depends on the user selection, we run Open Query , ( which is > joining three PF , and putting into an another PF), > and that PF is being used in the RPG. > > " We want to take out the Open Query, and create 19 different join > Logical file ( to join all 3 PF ) , with Selection creteria and with > Key Field" and use the join logical file to generate the report." > > PROBLEM. > I can able to create 19 different LF, over > 3 different PF. But, """" The Key filed used on the open query is > taking fields from all three PF, instead of only from the Primary > fille." > > I read in the manual that " I can use only > the fields from the Primary file for the Key field ", which is correct > because when I use fields from secondary files for Key field, I am > getting a compile time error. > > WHAT WE WANT TO DO > Basically we want to ellimate the process to generate the access path ( > to take out the open query file from CL ) every time when the user select > any option to generate the report. > > Actual Open Query File statement in CL > > OPNQRYF FILE((FILE 1) (FILE 2) (FILE 3)) + > > FORMAT(CRTPF1) QRYSLT('(X090 *EQ "R")') > > KEYFLD((XR180) (XR070) (XR110) (XA133) (XA131) > (XA010) (CHKDIG) (ICLASS)) > > JFLD((ALOAN DLOAN) (CLOAN DLOAN)) > > JDFTVAL(*YES) MAPFLD((ALOAN 'A2LOAN' *CHAR 10) > (MR010 'SMR010' *CHAR 9) (HKDIG 'CHKDIG' *CHAR 1) (DLOAN 'MR010 + > > *CAT HKDIG' *CHAR 10) (CLOAN 'ILOAN' *CHAR 10)) > > Any help is greatly appriciated. > > Thanks in advance > > Raj ___________________________________________________________________________ ______________________________________________ > > +--- > | This is the Midrange System Mailing List! > | To submit a new message, send your mail to MIDRANGE-L@midrange.com. > | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. > | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. > | Questions should be directed to the list owner/operator: david@midrange.com > +--- +--- | This is the Midrange System Mailing List! | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: david@midrange.com +--- +--- | This is the Midrange System Mailing List! | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: david@midrange.com +---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.