• 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
    Computer Keyes

    Create Full Color Overlays, Interactive PDFs

    KeyesOverlay rapidly converts standard *SCS printer files into eye-catching PDF documents. Create graphical overlays in full color (or black and white if preferred) then easily map your spooled file text with different fonts, sizes and colors!

    KeyesOverlay can also create Interactive Forms for your customers to fill out and email or submit back to you.

    www.computerkeyes.com

    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

  • JD Edwards Customers Face Support Decisions
  • Security, Automation, and Cloud Top Midrange IT Priorities, Study Says
  • Cleo and SrinSoft in Integration-Modernization Link Up
  • Four Hundred Monitor, August 3
  • IBM i PTF Guide, Volume 24, Number 31
  • Power10 Entry Machines: The Power S1022 And Power L1022
  • Highly Secure API Enablement for IBM i
  • Guru: IBM i Privileged Account Management, And What’s So Special About Special Authorities
  • IBM Finally Shows Some Growth In Sales And Profits
  • Altair Delivers More Options for Running SAS Code

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.