Re: SQL Question: Retrieving the last record in a file?

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.

On Thu, Apr 8, 2010 at 12:05 PM, CRPence wrote:

Luis Rodriguez wrote:

You could create a view that gives you the data you need
something like:

CREATE VIEW MyLib/MyViewName as
( Select AccNumber, Max(ChkNumber) AS LastCHK
From MyLib/MyFile Group by AccNumber )

This will work with RCVF. It is that what you would need?


On Wed, Apr 7, 2010 at 12:46 PM, <Rich_Weislak@xxxxxxxxx>
wrote:

I need to get the last record with a file by Account
Number, basically I need to get that last check number used
by Company Number, is there a SQL statement that I can use
for this, it will also be nice if could use it within a CL
program. Any suggestion would be nice.

That is an interesting interpretation of the described
scenario. I found what was offered by the OP so lacking [no
DDL, no DML for sample data], I could divine little of what was
actually desired. Also confusing, for mention of both "by
Account Number" and "by Company Number" for how to retrieve the
last check number; or was the former implying the key.? Just
for mention of "Company Number" but that there was no reference
to that column in the VIEW, seems suspect.?

Maybe this additional reply will evoke comment by the OP to
further clarify, if perhaps the original replies did not
already provide resolution.

For lack of mention of "for every" on something like the
Company Number or Account, I expected the scenario was for
retrieving just one row, such that there was need to
dynamically request a specific account or company by number.
The noted VIEW could have multitudes of rows for which RCVF
might then have to loop up to that same number of times just to
find one specific account number.

Anyhow, with those thoughts, an OPNQRYF inserted prior to the
RCVF might be of interest. Whatever is the eventual SQL SELECT
could be encapsulated in *QMQRY, optionally with variables to
allow input for the WHERE clause to effect any specific
selection, and the STRQMQRY OUTPUT(*OUTFILE) OUTMBR(*FIRST
*REPLACE) can make the input to the CLP be just like any
typical *OUTFILE with RCVF combination. There are numerous ways
the data can eventually get into the CL program, but what the
CL program[mer] wants to do, best dictates method.

--
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.