• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Let One Row Represent a Group

    April 28, 2010 Ted Holt

    Dear Esteemed and Highly Competent Professional,

    From time to time, I need to retrieve one row (record) to represent a group of rows that share similar characteristics. It’s a good technique, one that I’d like to share with the readers of this unpretentious publication.

    Assume a table (physical file) of sales order details. Each row represents a line of a customer order. Some customers submit an entire order at the same time, but others prefer to keep one order open with us, adding new lines to the order as needed. Here are the columns (fields) of the sales order detail file.

    Column      Type        Size   Description
    ==========  ==========  ====   =========================
    ORDERNO     Packed       5,0   Order number
    LINENO      Packed       3,0   Line number
    ITEMNO      Character    6     Item number
    QTYORDERED  Packed       7,0   Quantity ordered
    QTYSHIPPED  Packed       7,0   Quantity shipped
    PRICE       Packed       7,2   Unit price
    CRTTIME     Timestamp          Date/time created
    CRTUSER     Character   10     Created by user
    CHGTIME     Timestamp          Date/time of last change
    CHGUSER     Character   10     Changed by user
    

    We’ve been asked to list all open orders (those that have not been fully shipped), but we are only to show the earliest unshipped line. That is, an order may have a dozen unshipped lines, but only the first unshipped one is to be retrieved from the database.

    The unshipped lines are the ones where quantity shipped is less than quantity ordered. As for the earliest order line, that would be the one with the earliest creation timestamp.

    This first step is to find the key information for the earliest open line. If no two order lines within an order can have the same timestamp, then this select statement finds the first unshipped line of each order.

    select OrderNo, min(CrtTime) as CrtTime
         from salesordd
        where QtyShipped < QtyOrdered
        group by OrderNo
        order by OrderNo
    

    The result set looks like this:

    ORDERNO   CRTTIME
    =======   ==========================
      101     2010-04-21-11.15.49.000000
      102     2010-04-21-10.05.00.000000
      103     2010-04-21-14.37.31.000000
      105     2010-04-21-10.30.03.030000
    

    That’s great, but where are all the other fields on the line? They’re missing, because we can’t include non-grouping fields in a grouping query.

    The technique we need is to join this result set back to the dataset from which it came, using an inner join, like this:

    with FirstOpenOrderLine as
      (select OrderNo, min(CrtTime) as CrtTime
         from salesordd
        where QtyShipped < QtyOrdered
        group by OrderNo)
    
    select d.OrderNo, d.LineNo, d.QtyOrdered, d.QtyShipped,
           (d.QtyOrdered - d.QtyShipped) as Balance,
           d.Price,
           (d.QtyOrdered - d.QtyShipped) * d.Price as Extended,
           substr(char(d.crttime), 1, 16) as TimeEntered
      from salesordd as d
      join FirstOpenOrder as fool
        on d.OrderNo = fool.OrderNo
       and d.CrtTime = fool.CrtTime
     order by d.OrderNo, d.crttime
    

    The common table expression creates the result set we just saw, giving it the name FirstOpenOrderLine. The main select joins FirstOpenOrderLine to the table (or view) from which it was built, SALESORDD. The result set looks like this:

    ORDERNO LINENO ORDERED SHIPPED BALANCE PRICE EXTENDED TIMEENTERED
    ======= ====== ======= ======= ======= ===== ======== ===============
    101       2         12       0      12	1.00    12.00 2010-04-21-11.15
    102       1         10       5       5	2.00    10.00 2010-04-21-10.05
    103       3          5       0       5	1.10     5.50 2010-04-21-14.37
    105       2          6       0       6	2.00    12.00 2010-04-21-10.30
    

    Let’s take it a step further. Suppose two lines of an order can have the same timestamp field, down to the microsecond. Then what? Let’s make an executive decision that we’ll retrieve the row with the lowest line number. Our query changes slightly.

    with FirstTimestamp as
      (select OrderNo, min(CrtTime) as CrtTime
         from salesordd
        where QtyShipped < QtyOrdered
        group by OrderNo),
    
     FirstOpenOrderLine as
      (select d.OrderNo, d.CrtTime, min(LineNo) as LineNo
         from salesordd as d
         join FirstTimeStamp as fts
           on d.OrderNo = fts.OrderNo
          and d.CrtTime = fts.CrtTime
        where d.QtyShipped < d.QtyOrdered
        group by d.OrderNo, d.CrtTime)
    
    select d.OrderNo, d.LineNo, d.QtyOrdered, d.QtyShipped,
           (d.QtyOrdered - d.QtyShipped) as Balance,
           d.Price,
           (d.QtyOrdered - d.QtyShipped) * d.Price as Extended,
           substr(char(d.crttime), 1, 16) as TimeEntered
      from salesordd as d
      join FirstOpenOrderLine as fool
        on d.OrderNo = fool.OrderNo
       and d.CrtTime = fool.CrtTime
       and d.LineNo = fool.LineNo
     order by d.OrderNo, d.crttime
    

    The first common table expression gets the earliest order entry date of unshipped orders. The second common table expression gets the first line for each order and earliest date. The main select now includes another join field: line number.

    I used this technique recently, when I had to retrieve a field (column) from the first operation of each item’s routing. In my shop, the first operation is normally given a sequence of 10, and subsequent operations are numbered in increments of 10 to allow for insertions. Thus, the first operation in most routings is number 10, but the first routing for some items is operation five.

    I thought I had published this tip already, but apparently I have not, according to Google. If I did, the review won’t hurt.



                         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

    Fax Directly from your IBM i

    KeyesFax is a full function automated IBM i fax system. Spooled files are burst by fax number and auto transmitted with overlays.  It combines both a send and receive facsimile processing system with a complete image package.

    The fax software will edit, send, receive, display, print, and track fax documents or images using any standard IBM i without additional expensive hardware, software or subscriptions.

    Computer Keyes has been developing Software Solutions since 1978!

    www.computerkeyes.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    RevSoft:  Enterprise solutions for data transfers, messaging and scheduling
    American Top Tools:  Can you reach total machine CPW without having to change or upgrade?
    COMMON:  Join us at the annual 2010 conference, May 3 - 6, in Orlando, Florida

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Royal Caribbean Cruises the Seas with Updated JDE ‘Blue Stack’ Better Than a Sharp Stick in the i

    Leave a Reply Cancel reply

Volume 10, Number 14 -- April 28, 2010
THIS ISSUE SPONSORED BY:

ProData Computer Services
SEQUEL Software
WorksRight Software

Table of Contents

  • Files in Subprocedures
  • Let One Row Represent a Group
  • Admin Alert: Diary of a Production System Upgrade, Part 1
  • FAQs for MySQL and the DB2 Storage Engine
  • Yet Another Super-Easy Way to Copy a Spooled File from One Partition to Another
  • More on Whether IPLs Help System Performance

Content archive

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

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

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 © 2025 IT Jungle