• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Finding the Last Transaction for a Customer

    January 31, 2007 Hey, Howard

    I’ve been studying your book, SQL at Work. I was hoping you would answer a question for me. We have a historical transaction file sorted on a descending date field. I’d like to only select the latest record for a specific ID with the most current date. I don’t know the value of this date and I don’t want it to return any other records. Is this possible? Thanks!

    –Mary

    This kind of query can be done using a subquery or a join operation. Let’s create a sample table, load it with data, and try a few queries that demonstrate this technique. The following statement will create a transaction file that contains debit and credit transactions, customer identification number, and a quantity.

    CREATE TABLE SQLBOOK.TRANSTEST
    	(TRANSID INTEGER NOT NULL 
    PRIMARY KEY 
    GENERATED ALWAYS 
    AS IDENTITY,
    	 TRANSDATE DATE NOT NULL,
    	 CUSTOMERID INTEGER NOT NULL,
    	 TRANSTYPE CHAR(1) NOT NULL,
    	 QUANTITY DECIMAL(10,2) NOT NULL);
    

    Now execute the following statements to add data to the test table:

    INSERT INTO SQLBOOK.TRANSTEST 
    	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
    	VALUES ('01/01/1980',1,'C',100.33);
    INSERT INTO SQLBOOK.TRANSTEST 
    	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
    	VALUES ('02/02/1982',1,'D',10.21);
    INSERT INTO SQLBOOK.TRANSTEST 
    	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
    	VALUES ('03/03/1983',1,'C',1000.54);
    INSERT INTO SQLBOOK.TRANSTEST 
    	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
    	VALUES ('04/04/1984',1,'D',830.74);
    INSERT INTO SQLBOOK.TRANSTEST 
    	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
    	VALUES ('05/05/1985',1,'C',52.12);
    

    Ok, now that we have data, let’s think about a query strategy. In order to answer the query, you need two pieces of information, the customer number that you want to find the last transaction for and the date of the last transaction. To get the date of the last transaction for a customer, you can execute the following query:

    SELECT MAX(TRANSDATE) 
    FROM SQLBOOK.TRANSTEST
    WHERE CUSTOMERID = 1;
    

    Now, this query returns a single row with the date of the last transaction, to find that transaction record, we can join the result of this query to a query against the TRANSTEST file to find the rest of the field values in the target record. One syntax for accomplishing this is the following query:

    SELECT * 
    	FROM SQLBOOK.TRANSTEST AS A 
    		INNER JOIN
    			(SELECT MAX(TRANSDATE) AS MAXDATE 
    				FROM SQLBOOK.TRANSTEST 
    				WHERE CUSTOMERID = 1) AS B
    		ON (MAXDATE = TRANSDATE)
    	WHERE CUSTOMERID = 1 ;
    

    Note that we open the TRANSTEST table and join it to the results of our max date query. This type of query can also be accomplished using a subquery. Here is an example:

    SELECT * 
    	FROM SQLBOOK.TRANSTEST AS A
    	WHERE CUSTOMERID = 1 
    	AND TRANSDATE = 
    		(SELECT MAX(TRANSDATE) 
    		FROM SQLBOOK.TRANSTEST AS B
    		WHERE B.CUSTOMERID = A.CUSTOMERID);
    

    Note that this is equivalent to what is being performed in the first example, in fact the iSeries will recognize that the subquery is actually a join operation and choose to implement the query as a join. Hope this helps!

    –Howard

    Howard Arner is vice president of Client Server Development. He spends his days writing software that talks to the System i5, iSeries, and AS/400 databases from Microsoft’s .NET protocols. His book, SQL at Work is available at the IT Jungle online bookstore.

    RELATED STORY

    Retrieve a Representative of a Group



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Racksquared

    It’s time for IBM Power in the Cloud!

    Stop buying hardware and make the move to the cloud. It’s easier and more cost effective than you might think.

    • IBM Power in the Cloud
    • IBM Power Backup Solutions
    • IBM Power High Availability and DR solutions
    • IBM Power Colocation with Management and Monitoring

    Let’s talk about your business needs.

    Call: 855-380-7225

    Email: Sales@racksquared.com

    www.racksquared.com/ibmsolutions

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  Join us at the 2007 conference, April 29 – May 3, in Anaheim, California
    New Generation Software:  Leading provider of iSeries BI and financial management software
    Twin Data:  System console control for AS/400s and LPARs from a LAN, WAN, VPN, or via Internet

    Books on Sale at the IT Jungle Store: 30 Percent Off for 30 Days

    The System i Pocket RPG & RPG IV Guide: List Price, $69.95; Sale Price, $49.00
    The iSeries Pocket Database Guide: List Price, $59.00; Sale Price, $41.00
    The iSeries Pocket Developers' Guide: List Price, $59.00; Sale Price, $41.00
    The iSeries Pocket SQL Guide: List Price, $59.00; Sale Price, $41.00
    The iSeries Pocket Query Guide: List Price, $49.00; Sale Price, $34.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00; Sale Price, $27.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00; Sale Price, $34.00
    iSeries Express Web Implementer's Guide: List Price, $59.00; Sale Price, $41.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95; Sale Price, $56.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00; Sale Price, $62.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00; Sale Price, $34.00
    WebFacing Application Design and Development Guide: List Price, $55.00; Sale Price, $38.00
    Can the AS/400 Survive IBM?: List Price, $49.00; Sale Price, $34.00
    The All-Everything Machine: List Price, $29.95; Sale Price, $21.00
    Chip Wars: List Price, $29.95; Sale Price, $21.00

    Mainsoft Supports Visual Studio 2005, Mono 1.2 with Updated Grasshopper IBM Upgrades System i5 Disk Controllers, Adds Enclosures

    Leave a Reply Cancel reply

Volume 7, Number 4 -- January 31, 2007
THIS ISSUE SPONSORED BY:

Profound Logic Software
IBS
WorksRight Software

Table of Contents

  • Finding the Last Transaction for a Customer
  • Sorting Arrays and Subfiles with a User Index
  • When Fix Central Won’t Let You Download PTFs

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM Mulls Using DataMigrator as Cloud Warehouse Pipeline
  • PowerTech AV Automatically Detects Ransomware Activity
  • Infor Puts CM3 Project On Hold
  • Four Hundred Monitor, June 29
  • IBM i PTF Guide, Volume 24, Number 26
  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.