Re: Re. Join Logical File Problem -- MIDRANGE-L

Hi Raj,

Regarding Override:

As the mannual writes:

1. Overrides are ignored for the file and library specified on the 
FORMAT parameter, unless FORMAT(*FILE) is specified.

2. Parameter values specified on an override command, other than TOFILE, 
MBR, LVLCHK, WAITRCD, SEQONLY, or INHWRT and SHARE , are ignored by 
OPNQRYF command.

I think due of any of the above raesons(possibly 1), you are not able to 
see the records in file CRTPF1.

Thanks,

Rajeev.


From: Rajan.Srinivasan@Allfun.com
To: MIDRANGE-L@midrange.com
Date: Mon, 21 Dec 1998 23:54:31 -0500
Subject: Re. Join Logical File Problem
Reply-To: MIDRANGE-L@midrange.com

Walden,
                       Thanks for your   response.   Actually we use
Version   V4R2.
We  use  19  different  Open Query file  ( for the 19 different  
selection
creteria ), and  each uses its own KeyField.
In other words, if the user select   Selection 3,   then the CL builds
Open Query file for that selection creteria.

The key fields vary drastically, sometimes the primary file field, then
secondary file 1  field, then secondary file 2 field like that.

Open Query File statement

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




File   CRTPF1 which is a Physical File  which has fields from  all three
Physical files ( FILE 1, FILE 2, FILE 3).


Also, CRTPF1 is being overriden to  FILE 1,   before running OPNQRYF . I 
am
little bit confused to see after running
the  OPNQRYF, I did'nt see any records at   CRTPF1.

When ever  I select any selection to print a report ( out of tthose 19
selection ), I got the following message  interactively.

QUERY RUNNING,  Building access path for File  1  in  MYLIB
Query Running        ,  Creating  copy  of  File   *N  in   *N
Query  Running       ,   Building access path  for  file  *N  in  *N
345896  records selection.  Selection complete


I am sorry,  working as a consultant  I cant post the exact source. I 
will
check with my boss regarding that.

I will greatly appriciate, if you  shed some light on this.

Thanks again

Regards
Raj








From: Walden Leverich <walden@techsoftinc.com> AT Internet on 12-21-98
      10:13 AM

To:   MIDRANGE-L@midrange.com AT Internet@ccmail
cc:    (bcc: Rajan Srinivasan/Servicing/SuperiorBank)
Subject:  RE: Join Logical File Problem




Raj,

You are correct, you cannot use the fields from the secondary files as 
keys
in a join access path. However, the OPNQRYF should perform well, if 
tuned.
Can you post the DDS (or SQL) that was used to create file 1, 2 and 3? 
I'd
be interested to see which of your key fields come from which files. If 
the
correct LFs are build over files 1, 2 and 3 OPNQRYF will *usually* not 
need
to build an index to select the records, but rather to order them. Can 
you
also try running this query with debug on (STRDBG) and see what messages
the query optimizer returns? Finally, what version of the operating 
system
are you on?

-Walden

-----Original Message-----
From: Rajan.Srinivasan@Allfun.com [mailto:Rajan.Srinivasan@Allfun.com]
Sent: Friday, December 18, 1998 10:14 PM
To: MIDRANGE-L@midrange.com
Subject: Join Logical File Problem


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



______________________________________________________
Get Your Private, Free Email at http://www.hotmail.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
+---