AS/400 SQL transaction file help

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.

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

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);

Dig Deeper on DB2 UDB (universal databases)

Manage your SQL Server databases with SQL DMVs SQL DMVs (dynamic management views) can help simplify administration of SQL Server databases. Find out how with detailed examples.

Putting SQL Server catalog views to work SQL Server catalog views are system views that provide an interface to a wide range of catalog metadata, including information about security objects, full-text search, database mirroring, partitions and more. Read on for an in-depth look at several subcategories of catalog views.

What is a data rollup? Learn what a data rollup is and read about data rollup macro examples and case studies in this data mining tutorial from the book Data Mining: Know it All.

Automating Hibernating Mapping and Queries For Java Web Development Hibernate is a powerful library for mapping relational data to Java classes. Setting up the Hibernate object-relational mapping and writing basic Hibernate queries to create, read, update and delete table data can be tedious and time consuming. This paper introduces WaveMaker as a way to automate the generation of Hibernate classes, including support for managing related tables, creating custom HQL queries and defining custom Hibernate views.

Avoid cursors in SQL Server with these methods to loop over records SQL Server expert Matthew Schroeder adds his name to the list of DBAs and T-SQL programmers who wish cursors had never been introduced. Sure cursors have their place for data manipulation on a row-by-row basis. But are they worth the trade-off of poor performance in SQL Server? Schroeder shares three methods for looping over records without cursors.

How do I retrieve second transaction for each customer? An Oracle user asks how to write a query to retrieve the second transaction for each customer.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

Meet all of our AS/400 experts

View all AS/400 questions and answers

Please create a username to comment.

Refresh your data center cooling lexicon

Comparing Linux distributions: Red Hat vs. Ubuntu

Get eco-friendly with LEED data center certification

Dig Deeper on DB2 UDB (universal databases)

Related Q&A from Kent Milligan

Have a question for an expert?

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.