RE: Open queries

Shelly,

You have my sympathy. I've been using the OPNQRYF command for more years
than I care to remember, and I still find myself cursing at the QRYSLT parm
on a regular basis. :)

Presuming that DATE is your CL variable, and VAR1/VAR2 are fields in FNAME;
there are a couple of problems with the example that you posted:

1) When you refer to a CL variable, you need to prefix the name with an
ampersand, as in &DATE. That distinguishes it from a field within the file.

2) The QRYSLT is itself a parameter that accepts a string value. Therefore
you cannot "nest" another parameter within the string, because the OPNQRYF
command cannot reference it once it's running.

What you need to do, is to construct the QRYSLT string by concatenating the
various parts of your expression. If, as in this case, the expression
includes a CL variable, that variable must be declared as a CHAR.

Further complicating matters, is the fact that you need to choose between
single-quotes and double-quotes, based upon the data type of the field you
are comparing to.


For example:

If &VAR2 is a character or date variable:

QRYSLT('VAR1 *EQ "M" *AND VAR2 *EQ "' *CAT &DATE *CAT '"')


If &VAR2 is a numeric variable:

QRYSLT('VAR1 *EQ "M" *AND VAR2 *EQ ' *CAT &DATE *CAT ' ')


Rather than giving you a long winded explanation of why you need to do this,
I'll refer you to the following documentation:

http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/index.htm?info/db2
/rbafomst02.htm

This is a link to the "DB2 UDB for AS/400 Database Programming" manual. Go
to the following sub-section:

-Opening a database file
---Using the Open Query File (OPNQRYF) command
-------Selecting records without using DDS

There you'll learn the various rules, and you'll see the supporting
examples.


John Taylor
Canada


> Date: Wed, 9 May 2001 14:40:26 -0500
> From: Shelly.Sommerfeld@state.sd.us
> Subject: Open queries
>
> I am having a little bit of difficulty with the OPNQRYF.  What I
> would like
> to do is on the QRYSLT, I would like to have a parameter that is being
> passed into my CL as one and also would like to pick out of my
> file another
> field.
>
> Here is my example:
>
> OPNQRYF   FILE((FNAME)) QRYSLT('VAR1 *EQ "M" and also VAR2 *GE DATE ')  (
> that is being passed in)
>
> for some reason I am unable to get this to work.
>
> I hope that one of you could help me..
>
> Thanks
> Shelly Sommerfeld
> BIT Development Team 3
> (605) 773-5808
> shelly.sommerfeld@state.sd.us

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---