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

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