Evaluate
Weigh the pros and cons of technologies, products and projects you are considering.
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.
Can you help me with the best way to select the last three transactions made in a transaction file using AS/400 SQL?
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.
Step 2 of 2:
Is there something more efficient than: select top 3 CustID, TranDate, Amount from Trans where CustID = '1234'
order by TranDate desc
No, that would be the most efficient. However, DB2 for i5/OS supports the FETCH FIRST clause instead of the proprietary "TOP n" syntax.
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);
