how to create new result field by concatinating two or more fields in OPNQRY.
Hi All,
i want to create a new result field by concatinating two or more fields in OPNQRY for comparision.
In detail:
I have one tranaction file having millions on records however i want only records which were trasacted in last twodays.
I have 3 seperate fields in trans file as TRYEAR, TRMONTH, TRDAY for transaction date.
i am receiving 2 dates into my cl program
1. one is last cycle processed date (20090720) &
2. current cycle date (20090722)
now i want to write OPNQRYF on my transaction file to select records between these two dayes 20090721 and 20090722.
i have one RPGLE program to process records which ever selected in OPNQRYF.
Can any one please help how can code this one in OPNQRYF.
Software/Hardware used:
Software/Hardware used:
ASKED:
July 22, 2009 11:41 AM
UPDATED:
July 24, 2009 10:00 PM
Answer Wiki:
Hi,
To concatenate this 3 numeric fields just do
(TRYEAR*10000)+(TRMONTH*100)+TRDAY
Regards,
Wilson
----------------------------------
I normally create a QRYSLT field
<pre>
CHGVAR &NEWDATE VALUE((TRYEAR*10000)+(TRMONTH*100)+TRDAY)
CHGVAR &QRYSLT VALUE('&PROCESSDATE *GE ' *CAT + &NEWDATE *CAT ' *AND &NEWDATE *LE ' *CAT &CYCLEDATE ')
OPNQRYF FILE((XXXXXXX)) QRYSLT(&QRYSLT) .....
</pre>
===========================================
that's not quite it ...
If TRYEAR ,TRMONTH and TRDAY are in the file you're doing the open query on you have to pass the computational method not the value. And PROCESSDATE and CYCLEDATE are fields in the CL not the query file and are char types.
&Newdate is a char type!
CHGVAR &NEWDATE VALUE('((TRYEAR*10000)+(TRMONTH*100)+TRDAY))')
CHGVAR &QRYSLT VALUE(&PROCESSDATE *CAT ' *GE ' *CAT +
&NEWDATE *CAT ' *AND ' *cat &NEWDATE *LE ' *CAT &CYCLEDATE )
I think this is closer.
Phil
===============================================
The OPNQRYF will have something like that
OPNQRYF FILE((FILEA))
QRYSLT('FILEDATE = %Range(LASTCYCLE CURRENTCYCLE)')
MAPFLD((FILEDATE '(TRYEAR*10000)+(TRMONTH*100)+TRDAY'))
Of course, you should mount a the query string to insert LASTCYCLE and CURRENT CYCLE values in it but the most important is the MAPFLD parameter to achieve what you are looking for.
CHGVAR &QRYSLT VALUE('FILEDATE = %Range(' *CAT +
&NEWDATE *CAT ' ' *CAT &CYCLEDATE *CAT ')')
Wilson
===================================================
Wilson - that's great
Assumptions are
1. TRYEAR, TRMONTH and TRDAY are numeric in the file
if these are character then they need to be || togeter and a ''' added before and after
&NEWDATE and &CYCLEDATE
2. TRYEAR contains the 4 digit year ie. 2009
3. &NewDate and &CycleDate are char if they aren't create &NewDateC and &CycleDateC and use ChgVar to set them equal to the numerics.
Phil
==============================================================================
Satsho
That was a great start .. as you know OPNQRYF is a bear, it accepts and compiles almost anything and then doesn't give much of a hint on why threre aren't any records.
I think that Mohan K got his query to run, since he came back with performance questions.
Phil
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks Phil, Wilson
just a fyi – I did not check for the validity of the statements – it was more of ‘how-to’ code example. I will certainly try to correct that in the future