Re: Must I use OPNQRYF

While it is way better to put your SQL into the RPG program you can do
it directly from CL without OPNQRYF. You would put your SQL statement
into a variable and call a utility in QSHELL called DB2. So it looks like:

ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) +
VALUE('FILEAPPEND=/tmp/shell.log')
MONMSG CPFA980 /* Error adding value */
ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) +
REPLACE(*YES) +
VALUE(Y)
MONMSG CPFA980
CHGVAR&Command VALUE('db2 "'select * from whatever...etc'"')


QSH cmd(&Command)

Works like a charm, and really, it's too easy. The hardest part is
getting the quote ticks to work right.

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On 2/20/2012 6:12 AM, Vern Hamberg wrote:

Hi Dave

Anywhere you can use OPNQRYF, you can easily use SQL. I once (15 years
ago) wrote an internal document that mapped the parameters of OPNQRYF to
the clauses of a SELECT statement - it's pretty obvious - QRYSLT is a
WHERE clause, KEYFLD is ORDER BY, GRPSLT is GROUP BY, MAPFLD maybe an
expression with an ALIAS, etc.

It seems that to use embedded SQL, you'd have to pass the product code
in question to the RPG program, for use in the WHERE clause of the
SELECT statement. That is probably not that big a deal.

You could also create a general-purpose SQL statement processor to use
in CL that'd create an OUTFILE with the selected records. That processor
would use QM queries - query management queries (QMQRY object type). You
can find an example at

http://www.mcpressonline.com/programming/cl/the-excsqlstm-utility.html

- the code is there. There is a flaw in this one, in Figure 2. The
problem is that if your SQL statement is over 550 characters, a blank
will be inserted at position 551, or 1 past any multiple of 550. There's
an easy-enough fix - leave no white space at the end of each line in
that figure. Instead, fill them like this - record length is 91, so the
data field is 79 long.

&S01&S02&S03&S04&S05&S06&S07&S08&S09&S10&S11&S12&S13&S14&S15&S16&S17&S18&S19&S2
0&S21&S22&S23&S24&S25&S26&S27&S28&S29&S30&S31&S32&S33&S34&S35&S36&S37&S38&S39&S
40&S41&S42&S43&S44&S45&S46&S47&S48&S49&S50
------------------------------------------------------------------------

Yes, not as pretty, but this one works - the other one doesn't under
certain conditions.

BTW, I'm doing a COMMON session on this technique this year - check the
schedule atwww.common.org

Oh yeah, why stop using OPNQRYF? Things like the JOINFLD parameter allow
only an INNER JOIN - that's one example of how OPNQRYF doesn't have the
flexibility of SQL - just like Query for i has limitations. They are
both excellent methods with limits as chosen by the product designers
and developers at IBM.

HTH
Vern

On 2/20/2012 4:46 AM, Dave wrote:

Hi,

I have an rpg called by a clp. It just reads a file and prints a report.
The file existed in several different libraries, now all the files are
fusioned to make one big one with a product code field added to be able to
distinguish between the different records. I will probably just add an
opnqryf to select the records with the right product code. I don't think I
could easily use SQL here, but am I wrong?

--