Re: Join Logical File Problem -- MIDRANGE-L

A solution that will eliminate the creation of access paths is to create an
SQL View over the 3 tables.  You will also have to use embedded SQL to
execute the query.  If the tables are indexed properly by the columns used
in the WHERE and ORDER BY clauses in your query, the query will not create
an access path.  The problem you have faced is one primary reason why SQL
Views are more practical and powerful than OPNQRYF and LFs.

Good luck,
Dave Duerr


-----Original Message-----
From: Rajan.Srinivasan@Allfun.com <Rajan.Srinivasan@Allfun.com>
To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
Date: Monday, December 21, 1998 10:40 AM
Subject: Re: Join Logical File Problem


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