×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using query result fields in CLP

Using query result fields in CLP

Using query result fields in CLP

(OP)
I've been asked to make a modification to an online screen that uses OPNQRYF on a file to generate a report so that the user can enter a date range to extract specific data. The query is currently run within a CL program and the QRYSLT statement is built inside the program depending on what the user enters in the screen.

The problem is that the file I'm using in the query stores the date separately in two digit fields(CC, YY, MM, DD). I decided to create a query and just run it in the CLP. I converted the date fields from numeric to text and concatenated them together in a Result Field as @DATE with format yymmdd.

In the CLP I added this line to the QRYSLT statement:
       ' *AND ' &EnteredDTE ' *GE ' &@DATE

The job goes into MSGW and checking the log I saw that while the Entered date does get converted to text and stringed together as well as added to the Select statement the statement is incomplete because &@DATE is blank:
        *and 070101 *GE)

Are there any other way to use a result field in a CL program?  

RE: Using query result fields in CLP

(OP)
Here's the error according to the job log:

 Message . . . . :    11500 - OPNQRYF FILE((BNISLS)) QRYSLT('PRTCOD *NE "F" *AND 040101 *GE           *AND 050101 *LE')

RE: Using query result fields in CLP

(OP)
Here's what I'm trying to do:

CHGVAR &FROM VALUE(&enteredDateFrom ' *le ' &DATE)
CHGVAR &TO VALUE(&enteredDateTo ' *ge ' &DATE)
OVRDBF FILE(SALES) SHARE(*YES)
OPNQRYF SALES OPTION(*ALL) QRYSLT(&QSEL)

If Entered MM, DD = 00
   call prog1
   CLOF
Enddo

If Entered MM, DD > 00
   STRQMQRY MYQUERY OUTPUT(*OUTFILE) ALWQRYDFN(*YES)SETVAR(
     (COND0 QSEL) (COND1 &FROM) (COND2 &TO))
   call prog2
Enddo

MYQUERY uses SALES for input and contains a Result Field
    called DATE.
YY, MM, DD are numeric fields found in SALES.
DATE is formed by converting YY, MM and DD to CHAR and
    using *CAT to string them together in YYMMDD format.

I figure that the FROM and TO conditions have a missing operator since DATE has not yet been read but I'm trying to use it as a select statement in STRQMQRY. I've tried putting a RUNQRY immediately after the IF statement but I get a SHARED OPEN OF FILE FAILED. My coworker suggested that I need to find a way to copy the results of the OPNQRYF to a file and use that file to RUNQRY so that DATE can be read.

RE: Using query result fields in CLP

I don't see where you have put a value into QSEL for the OPNQRYF statement.  If you put &FROM and &TO into it with &DATE blank I expect you wuld get the error you describe.

I not used STRQMQRY much, but I suspect you might make it work by chaging these lines.

CHGVAR &FROM VALUE(&enteredDateFrom ' *le &DATE')
CHGVAR &TO VALUE(&enteredDateTo ' *ge &DATE')

leaving the &DATE as a constant that gets subbed in later.

RE: Using query result fields in CLP

(OP)
I tried using OPNQRYF and came up with this statement:

OPNQRYF FILE option(*ALL)
             qryslt(&QSEL)
             keyfld(KEY1)
             mapfld((CRTDTE '%digits(YY) || %digits(MM) ||
                %digits(DD) *char 6))

I did as suggested and did the Select statement this way:
chgvar &qsel value(&qsel || '*and &DATE *ge ' ||
       &enteredDateFrom)
but the query is still extracting the complete report instead of just recoreds within the range.

I tried hardcoding CRTDTE < enteredDate into the QRYSLT itself but the it doesn't seem to affect the query.

RE: Using query result fields in CLP

Usually when OPNQRY runs, but you get unselected data, its because you didn't specify OVRDBF FILE(myfile) SHARE(*YES) before running the query.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close