Using query result fields in CLP

[Ju5]

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?

 

[Ju5]

Here's the error according to the job log:

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

 

[Ju5]

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.

 

[arrow483]

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.

 

[Ju5]

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.

 

[arrow483]

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