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

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.