× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.