-- Looks like an SBT system (or the newer AccPac since buyout).
Anyhow, from YOUR cursor, try the following
select a1.*;
from lcCursor a1;
where a1.InvNo IN ;
( select Max( b1.Invno );
from lcCursor b1 );
into cursor C_NewResultSingleInvoice
However, from your sample, it looks like your process is actually creating a cursor called "lcCursor" instead of "Report". To correct this, you will need to change your INTO clause to Into cursor ( lcCursor )
Then, the select I had above will ALSO have to include the () around the lcCursor to match your query.
>I need to list the last items purchased by customer. The sales transaction data is stored two separate files ARTRAN(Current Invoice Transactions) and ARYTRN(History Invoice Transactions). I have been able to create one cursor with data from the current and history files.(See Attached) How can I modify this to only retrieve items from the last invoice?
>
>lcDirectory = "D:\demo\pro73\data01\"
>lcCursor = "Report"
>
>SELECT INVNO,;
> ITEM,;
> CUSTNO,;
> LOCTID,;
> DESCRIP,;
> COST,;
> PRICE,;
> QTYORD,;
> QTYSHP,;
> INVDTE,;
> EXTPRICE,;
> CURRHIST ;
> FROM (lcDirectory + 'ARTRAN01') tran1 ;
> WHERE currhist = " " ;
>UNION ALL;
>SELECT INVNO,;
> ITEM,;
> CUSTNO,;
> LOCTID,;
> DESCRIP,;
> COST,;
> PRICE,;
> QTYORD,;
> QTYSHP,;
> INVDTE,;
> EXTPRICE,;
> CURRHIST ;
> FROM (lcDirectory + 'ARYTRN01') ytrn1 ;
>into cursor lcCursor