On 24-Mar-2012 01:44 , Raja Ayyer wrote:
<<SNIP>> a question re OPNQRYF: One of my CL program creates 2
temporary files in QTEMP:

one containing a before image of data (FILE_1) from a physical file
and the

other containing after image (FILE_2) from the same physical file.

So both FILE_1 and FILE_2 have the same Record Format?

After creation of these temporary files in QTEMP,

Are these files created using DDS sources, or CRTDUPOBJ from existing database *FILE objects? What is the status of the source or objects [from which the files in QTEMP are derived] as far as restrictions on /promotion/ with this scenario?

the program then does an OPNQRYF joining these two temporary files to
extract data into a third file (FILE_3).

So did the program create FILE_3 also? Same questions as above, for how.

Perhaps to clarify, that join described using SQL?

So CPYFRMQRYF TOFILE(FILE_3) from the join query ODP.? And either OPNQRYF FORMAT(FILE_3) was used, or FORMAT(*FILE) causes the query to use the RcdFmt of FILE_1, per FILE_1 being the first file in the join? Is the record format of FILE_3 different than the FILE_1 and\or FILE_2, and if so, how?

I have a requirement whereby the third file (as a result of the
OPNQRY) contains most of the information from FILE_1 and one field

What about the scenario and\or the join makes that a requirement? Perhaps because the field ECESDATE is a\the join field? Or perhaps as an effect /compare/ of before\after effects, the desire is to show a copy of both the before and after values for column ECESDATE in the final output [file]?

Perhaps a very simplified script showing the CREATE TABLE requests to describe each of the three files, i.e. their record formats, and what join type\logic will be performed, would help to clarify.?

Here I give an example of such a script. This script assumes:
- FILE_1 and FILE_2 are effectively the same; minor differences
- FILE_3 is as-yet undefined; i.e. coder still struggling
- desire to show before\after of column ECESDATE including deleted rows, but ignoring added rows


create table qtemp/file_1
( c char(1), d dec(1), dt dec(8), mab char)
; -- MAB is a column in FILE_1 not in FILE_2
insert into qtemp/file_1 (d) values(1),(2),(3),(4),(5),(6)
update qtemp/file_1 set c=d, dt=d, mab=d
create table qtemp/file_2
( c char(1), d dec(1), dt dec(8), mbn numeric)
; -- MBN is a column in FILE_2 not in FILE_1
insert into qtemp/file_2 (d) values(1),(3),(5),(6),(7)
; -- drop two rows, add one
update qtemp/file_2 set c=d, dt=d, mbn=d
update qtemp/file_2 set dt=d*100 where d in (3, 5)
; -- after image of 3 and 5 are changed for DT
create table qtemp/file_3
( c char(1), d dec(1), dt dec(8), mab char)
; -- unclear on requirement or possibilities for FILE_3
select t1.*, t2.dt /* CREATE TABLE AS mimics CPYFRMQRYF */
from qtemp/file_1 t1 left outer join qtemp/file_2 t2
on t1.c = t2.c
where t1.dt is distinct from t2.dt
/* or: t1.dt<>t2.dt or t2.dt is null */
; -- Perhaps this is the desired effect of the OPNQRYF ?
MAPFLD((MAB '2/dt') /* avoid CPD3107 dup flds */
(C '1/C') (D '1/D') (DT '1/DT') )
QRYSLT('((1/dt *NE 2/dt) *OR (2/dt *EQ %NULL))')
; -- Almost, but MAB as Char(1) can not represent 2/DT data
call QZDFMDB2 /* per QTEMP, use undocumented CALL */
PARM('alter table qtemp.file_3 add column dtchg dec(8);')
; -- Undesirable; alternative desired, per QTEMP not via QSH
MAPFLD((MAB '2/dt') /* use new field DTCHG */
(C '1/C') (D '1/D') (DT '1/DT') )
QRYSLT('((1/dt *NE 2/dt) *OR (2/dt *EQ %NULL))')
; -- Now both before and after for DT available as w/ SQL
cpyfrmqryf lojo tofile(qtemp/file_3) mbropt(*replace)
; -- destination for [report-like] data is FILE_3 in QTEMP
runqry *n qtemp/file_3
; -- the report of the data; same as what the SQL effected:
2 2 2 2 -
3 3 3 3 300
4 4 4 4 -
5 5 5 5 500
******** End of data ********


This field also exists in FILE_1 which is also required to be
extracted into FILE_3.

The OPNQRYF file allows file qualifiers for the fields. The MAPFLD allows mapping the data from one field into another.

However, during runtime, OPNQRYF is complaining about the same field
(ECESDATE) existing in both the files (FILE_1 and FILE_2).

Qualify the field with the file number in the Map Field just like with the Join Fields.

Due to restrictions in promoting to the production environment, I
have not considered using RUNQRY or even RUNSQLSTM (because of other
object overheads involved in promotion). Can this be achieved in

Very likely possible. Probably significantly easier to effect the promotion of a *QMQRY or some program for the CLP to CALL.? Mostly because the CLP is making the final or a temporary copy instead of the data instead of passing the shared join query ODP to another program.

Although likely possible to accomplish whatever within the CL without /promotion/ of more objects, more specifics about what is available already without promoting new objects into production would be of value to know first, to better explain how, without too much guessing. Also, is the QSHELL available\installed for use if desirable?

Anyhow... Perhaps answer some of the questions about the record formats, and give some examples of differences between them if they exist, plus give the OPNQRYF request being used [attempted] to accomplish the join. IMO, *best* [as in my example <code>] to provide the answers as the scripted SQL that would do all of the work, followed by what is attempted as the equivalent CL requests [or as best as can be described by the OP] that should mimic that scripted SQL.

Regards, Chuck

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 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].