Re: Must I use OPNQRYF

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

This is maybe even easier than using QM queries - I need to look at it more.

On 2/20/2012 9:04 AM, Jim Oberholtzer wrote:
> 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?
> > --