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

Chuck,

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, moreover since that OPNQRYF
uses (IIRC) the "old" query engine. 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).

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

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Thu, Apr 8, 2010 at 12:05 PM, CRPence <CRPbottle@xxxxxxxxx> 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.

Regards, Chuck
--
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.