how to create new result field by concatinating two or more fields in OPNQRY.

695 pts.
Tags:
AS/400
CLLE
OPNQRYF
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Satsho
    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 :)
    1,245 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following