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