×
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

Creating a string for QRYSLT parameter of OPNQRYF

Creating a string for QRYSLT parameter of OPNQRYF

Creating a string for QRYSLT parameter of OPNQRYF

(OP)
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!

RE: Creating a string for QRYSLT parameter of OPNQRYF

CODE

             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.

RE: Creating a string for QRYSLT parameter of OPNQRYF

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.

RE: Creating a string for QRYSLT parameter of OPNQRYF

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.

RE: Creating a string for QRYSLT parameter of OPNQRYF

(OP)
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?

RE: Creating a string for QRYSLT parameter of OPNQRYF

(OP)
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.

RE: Creating a string for QRYSLT parameter of OPNQRYF

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

RE: Creating a string for QRYSLT parameter of OPNQRYF

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.  

RE: Creating a string for QRYSLT parameter of OPNQRYF

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.

RE: Creating a string for QRYSLT parameter of OPNQRYF

(OP)
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?

RE: Creating a string for QRYSLT parameter of OPNQRYF

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

RE: Creating a string for QRYSLT parameter of OPNQRYF

(OP)
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?

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