AS/400 SQL transaction file help
Iseries database expert Kent Milligan offers an index for an efficient way of selecting the last transactions in a transaction file.
Example of file structure:
CustID, TransDate, Amount.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Is there something more efficient than: select top 3 CustID, TranDate, Amount from Trans where CustID = '1234'
order by TranDate desc
SELECT custid, transdate, amount from Trans Where CustID='1234' FETCH FIRST 3 ROWS ONLY
To make sure you get the best performance, you want to ensure the optimizer has the best index to implement this query. In this case, I'd recommend creating the following index:
CREATE INDEX myix ON trans(CustID, TranDate Desc, amount);