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

[CRPence <CRPbottle@xxxxxxxxx>]

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