× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Well, I often need to take a cat by the narrative and extinguish it! O:-)

Vern

Luis Rodriguez wrote:
Vern,

Yes, SETVARs are another option, thanks for reminding me that. As you
americans used to say, there is more than one way to be cruel to a feline
:-)

Regards,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Fri, Apr 9, 2010 at 1:06 PM, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:

Luis

Another option for using variables in an SQL statement is a QMQRY. You
can't read the results the same way as a view - thank you for that
information - but the STRQMQRY has SETVARs, where you set the
substitution variable values. This is very nice for doing reports (using
a related QMFORM) or for various utility functions with SQL.

Vern

Luis Rodriguez wrote:
Chuck,

(Last post on this thread, unless someone wants additional comments/info)


<snip/>
Here I should probably state an apology in advance... for even
responding to your post, since I will invariably have unnecessarily
extended the discussion, and probably without adding much value.
While I encourage and use the SQL, I guess I just felt like
defending the use of OPNQRYF for\from a CLP.
<snip>

No apologies are needed. Your points are all valid. A problem that I have
with OPNQRYF (not really applicable to the OP's problem) is that many
times
I have used it has been creating dynamic (and sometimes complex) QRYSLT
sentences, in order to include variables. Something that sometimes is
easier
to do with embedded SQL. Also, easier for others to understand and
maintain.

Regarding the use of REXX, although I do like it and have even done some
utilities with it, for reasons unknown to me this language is almost
unknown
to the midrange community here in Venezuela so, again, I try not to use
it.
In my 32+ years of working with computers this is something I have
learned:
There are many possible solutions to every business case, many times the
outcome simply something of personal preference BUT, exploring different
alternatives (and commenting them with colleagues) is makes this job
interesting.


Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Fri, Apr 9, 2010 at 3:19 AM, CRPence <CRPbottle@xxxxxxxxx> wrote:


Luis Rodriguez wrote:

As the OP seemed to need to read the data from a CL, I thought
he wanted to do it in a sequential fashion, hence the RCVF.
Anything else (ie., Random access), I tend to think in terms
of SQLRPGLE,

By deferring to SQL, there is little choice of random versus
sequential. Although RCVF from the VIEW may be /perceived/ as
sequential, it may be implemented by the database query engine as
either random access or sequential via a temporary file; but even
via a temporary for the GROUP BY query, that would probably have
been generated by random access methods.


moreover since that OPNQRYF uses (IIRC) the "old" query engine.

OPNQRYF does [still] use only the CQE, never the SQE. However,
"old" is also "tried and true"; "old" is not inherently negative.

To use only OS provided CL commands while making no external
permanent object references [i.e. no CALL of an SQLRPGLE program, no
invoking a script in a source member, no new SQL VIEW previously
created, no *QMQRY, etc.] other than the original database *FILE
that needs to be queried, using the RCVF over an ODP created by
OPNQRYF is about the best chance as a way to get a specific row of
data from a file into CLP variables.

FWiW: Much of what I have written has required references to only
what was available in the base OS and ideally self-contained in a
single source to minimize the user actions required to get the code
to run on any system in the world. There was a low probability that
an HLL compiler other than CL would be available, so embedded SQL
was rarely a preference; generally I would code only REXX with SQL,
or CLP with OPNQRYF. When I decided multiple objects would be
required, then typically *QMQRY were used, and the single source
delivered was a CL batch stream which would issue CRTQMQRY from
inline source [i.e. //BCHJOB with //DATA statements].


Also, I personally find an embedded SQL RPGLE program easier to
code and maintain than an OPNQRYF command, which could get very
complex easily (although maybe not in this particular case).

Many would agree. I typically would use whatever I find easiest
for the task at hand. If I am writing a stand-alone CLP, I do not
often think about what non-CLP I might write additionally, just to
achieve the end result. If I want to code something that is
predominantly CL requests but have some query requirements that are
better achieved by SQL than OPNQRYF, then I would use REXX. If I
was writing an application that had almost no CL requests to issue,
[irrespective of any need to use SQL,] then RPG would probably be my
choice.

I had many OPNQRYF that ran unchanged for well over a decade, and
for the few changes I did make, I was not any more challenged by
those than I am by the syntax & BIFs for RPG, less challenged with
SQL only because SQL is more pervasive having some consistency under
standards; i.e. the SQL might be used outside the i, whereas with
either RPG or esp. OPNQRYF, improbably. However I will note that
people ask the most basic questions of the SQL, questions which are
little different than those for using the OPNQRYF. As the OPNQRYF
request gets complex, so too does the corresponding request in
SQL... No?


Nevertheless, you are right: We need more info from part of the
OP in order to be able to offer a better solution.

Yep. And why I posted, hoping to prod the OP.

Here I should probably state an apology in advance... for even
responding to your post, since I will invariably have unnecessarily
extended the discussion, and probably without adding much value.
While I encourage and use the SQL, I guess I just felt like
defending the use of OPNQRYF for\from a CLP.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.