Creating a string for QRYSLT parameter of OPNQRYF

[Ju5]

I am trying to make a reusable CL program that will automatically generate a report monthly by executing a query. The query will use a date range from day 01 to day 99 of the previous month(yyyy(mm-1)'01' and yyyy(mm-1)'99').

The problem starts when I try to execute my query:

OPNQRYF FILE((&QUERY)) OPTION(*INP) +
QRYSLT('DATE *GE ' *CAT &FROM *CAT ' *AND DATE *LE +
' *CAT &TO *CAT ' *AND ITEM *EQ %VALUES( +
''z007349459'' ''z007358'') *AND TYPE *NE ''N''')

DATE is a field name in the table.
&FROM = yyyymm-1 + '01'. CHAR
&TO = yyyymm-1 + '99'. CHAR

I've already tried to string all the conditions into one variable but I still get an error message.
Here's how I tried to combine the conditions into one query:

&SEL1 = ('DATE *BCAT '*EQ %RANGE(&FROM &TO)'') <-
&SEL2 = *AND ITEM *EQ %VALUES( +
''z007349459'' ''z007358'')
&SEL3 = *AND TYPE *NE ''N'''

CHGVAR &QRYSLT VALUE(&SEL1 *BCAT &SEL2)
CHGVAR &QRYSLT VALUE(&QRYSLT *BCAT &SEL3)

OPNQRYF (&QUERY) OPTION(*INP) QRYSLT(&QRYSLT)

What am I doing wrong? Any help would be appreciated. Thanks in advance!

 

[flapeyre]

             OPNQRYF    FILE((&QUERY)) OPTION(*INP) QRYSLT('DATE = + 
                          %RANGE(' || &FROM *BCAT &TO || ') *AND +   
                          ITEM = %VALUES("z007349459"  "z007358") +  
                          *AND TYPE *NE "N"')

I have found it easier to quote a literal in double quotes than in doubled-up single quotes.

When I am having trouble with an OPNQRYF statement, I put a DMPCLPGM in the program. Usually, I build the entire query select to a variable and just use that in place of the QRYSLT parameter; then the dump will show me what the select looks like and I can usually see where I'm doing something wrong.

Tibi gratias agimus quod nihil fumas.

 

[arrow483]

You are on the right track. Build the &QRYSLT, then dump it back to the user using SNDMSG or SNDPGMMSG so you can see how it end up. Most likely you have a problem with the quotes not being where you need them.

Another method is to declare a variable &Q with a value of a single quote, then *CAT &Q where you need it.

 

[arrow483]

I'll bet your problem is here

CHGVAR &QRYSLT VALUE(&SEL1 *BCAT &SEL2)
CHGVAR &QRYSLT VALUE(&QRYSLT *BCAT &SEL3)

What you want is *TCAT. Add any blank needed to the front of the next variable. If you use *BCAT or *CAT the entire value of &SEL1 gets used (including trailing blanks). When the *BCAT completes, it doesn't fit into the result variable.

 

[Ju5]

What's the difference between *TCAT and *BCAT? I'm trying to put the field names(DATE, ITEM) into variables so that the can be used by different calling programs. Am I using %RANGE and %VALUE correctly?

 

[Ju5]

Okay. Here's what it looks like now:

&qote = '''
&nd = ' *AND '
&DATSEL = DATE(field found in the table)
CHGVAR VAR(&QRYSLT) VALUE(&DATSEL *CAT ' *GE ' *CAT +
&QOTE *CAT &FROM *TCAT &QOTE *CAT +
&ND *CAT &DATSEL *CAT ' *LE ' *CAT +
&QOTE *CAT &TO *TCAT &QOTE )

In case there are additional select statements I use the following:

IF COND(&SEL1 *NE ' ') THEN(DO)
CHGVAR VAR(&QRYSLT) VALUE(&QRYSLT *TCAT &ND +
*CAT &SEL1)

where &SEL1 is the additional statements. There are up to 5 optional statements.

The error I'm now receiving is this:
Field DATE on QRYSLT parameter not found.

 

[Ju5]

Is there a way to use the &QRYSLT I've created using RUNQRY?

 

[arrow483]

I don't see a problem with the select if date is a character field. If DATE is numeric, you have too many quotes. Check the value of &QUERY is correct, and that DATE is in fact a character field.

When trying to debug the &QRYSLT value, you might want to run SNDMSG MSG(&QRYSLT) TOUSR(youruserid) to see if it looks correct, before the query.

*TCAT trims the first variable, than cats the second variable. *BCAT also trims the first, then adds a blank, then adds the second variale. So you were ok to use *BCAT where you needed the blank. (I was thinking *CAT, which does not trim).

I don't know how you could "test run" the variable.

 

[flapeyre]

Are the dates in your select statement *CHAR? They need to be. You are building a string. Also, they need to be in the proper format for the date format in the fields you are using, with separators if they are native dates.

Tibi gratias agimus quod nihil fumas.

 

[Ju5]

The dates are *CHAR. A colleague suggested that OPNQRYF was not able to find the DATE field causing the "Field DATE on QRYSLT parameter not found. The query I am using for &QUERY uses three files and field DATE is on the third file. Any suggestions on how I can make sure that all the fields I need can be found without having to redefine the query in OPNQRYF?

 

[arrow483]

That does seem to be what the message is saying. Are you sure &QUERY is specified correctly? Do you have the join fields specified correctly? The format of the multi-file query is OPNQRYF FILE((&FILE1) (&FILE2) (&FILE3)) QRYSLT(&QRYSLT) followed by join criteria and format definition. Perhaps you could copy and paste your entire OPNQRYF line here

 

[Ju5]

First of all I would like to thanks you guys for your help. It really helped me think things through.

I am trying to make a reusable program so I was trying to avoid hardcoding the files and join criteria. I've moved to using STRQMQRY and I have succeeded in getting &QUERY to run. Now all I need is to somehow import the Report Column Formatting I set from the STRQRY to QMQRY. You guys have any idea on how to import the settings or should I should recreate the query and the settings on STRQMQRY?