• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Have Your Cake and Eat It, Too

    August 1, 2007 Hey, Ted

    Recently one of my superiors walked into my cubicle and asked for some information. I was in the middle of another task and didn’t want to be side-tracked, so I hurriedly ripped out an SQL query to satisfy his request. A little while later he was back in my office asking me to run a fresh copy of the query, and while I was at it, to total some of the columns. This last request threw me for a loop, so I used Query/400. Could I have satisfied his request with SQL?

    –D

    I thought I had covered this situation in a previous edition of Four Hundred Guru, but I’ve googled and googled and I can’t find it. Looks like this old geezer’s got some bad RAM. It’s a wonder I remember my kids’ names.

    SQL can list details or generate summary information, but not in the same SELECT. To pull this off, you need two SELECT statements–one to generate the detail lines and one to generate the totals. You can use UNION to combine them into one result set. Here’s an example that uses the QCUSTCDT file in library QIWS.

    SELECT LSTNAM, INIT, CUSNUM, CITY, STATE, BALDUE 
    FROM QIWS/QCUSTCDT
    UNION 
    SELECT 'ZZ', ' ', 0, ' ', ' ', SUM(BALDUE) 
    FROM QIWS/QCUSTCDT
    ORDER BY 1
    

    The first SELECT lists the records in the file. The second SELECT totals the balance-due column. Notice that I had to load the non-grouping fields with dummy values. I put a dummy ZZ value in the first column, to force the total to the bottom of the report. (ORDER BY 1 sorts on the first column.) I put blanks or zeros into the other non-grouping columns, depending on data type.

    This is the report I got.

    LSTNAM    INIT   CUSNUM   CITY    STATE     BALDUE
    Abraham   M T   583,990   Isle     MN       500.00
    Alison    J S   846,283   Isle     MN        10.00
    Doe       J W   475,938   Sutter   CA       250.00
    Henning   G K   938,472   Dallas   TX        37.00
    Johnson   J A   938,485   Helen    GA     3,987.50
    Jones     B D   839,283   Clay     NY       100.00
    Lee       F L   192,837   Hector   NY       489.50
    Stevens   K L   389,572   Denver   CO        58.75
    Thomas    A N   693,829   Casper   WY          .00
    Tyron     W E   397,267   Hector   NY          .00
    Vine      S S   392,859   Broton   VT       439.00
    Williams  E D   593,029   Dallas   TX        25.00
    ZZ                    0                   5,896.75
    

    You can also include subtotals. Here’s a different query over the same file. This time the file is sorted on state abbreviation. There are three selects. The first and last are similar in function to the SELECT clauses in the previous example. The middle SELECT generates subtotals by state.

    Notice the last column of the report. It is blank for detail lines, one asterisk for a state subtotal, and two asterisks for the grand total. The stars have two purposes. They allow me to sort the report in the proper sequence (column 6 is the second sort field), and they make the subtotals and grand totals stand out.

    SELECT STATE, LSTNAM, INIT, CUSNUM, BALDUE, ' '  
    FROM QIWS/QCUSTCDT                               
    UNION SELECT STATE, ' ', ' ', 0, SUM(BALDUE), '*'
    FROM QIWS/QCUSTCDT                               
    GROUP BY STATE                                   
    UNION SELECT 'ZZ', ' ', ' ', 0, SUM(BALDUE), '**'
    FROM QIWS/QCUSTCDT                               
    ORDER BY 1,6                                     
    

    Here’s the report.

    STATE  LSTNAM    INIT  CUSNUM    BALDUE Constant value
    CA    Doe       J W   475,938   250.00         
    CA                          0   250.00  *
    CO    Stevens   K L   389,572    58.75          
    CO                          0    58.75  *
    GA    Johnson   J A   938,485 3,987.50          
    GA                          0 3,987.50  *
    MN    Abraham   M T   583,990   500.00          
    MN    Alison    J S   846,283    10.00          
    MN                          0   510.00  *
    NY    Jones     B D   839,283   100.00          
    NY    Lee       F L   192,837   489.50          
    NY    Tyron     W E   397,267      .00          
    NY                          0   589.50  *
    TX    Henning   G K   938,472    37.00          
    TX    Williams  E D   593,029    25.00         
    TX                          0    62.00  *
    VT    Vine      S S   392,859   439.00          
    VT                          0   439.00  *
    WY    Thomas    A N   693,829      .00   
    WY                          0      .00  * 
    ZZ                          0 5,896.75  **       
    

    These reports are by no means elegant, but I learned years ago that people who are looking for numbers don’t usually care what the report looks like.

    –Ted



                         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
    Fresche Solutions

    Move Projects Forward with Expert Staffing Services

    Gain access to IBM i experts to move IT projects forward, reduce backlog and support business-critical systems.

    Fast onboarding, flexible engagement models for IBM i, RPG, COBOL, CA 2E (Synon), ERPs and more:

    • Bug Fixes & Maintenance
    • Full-Stack Web and Mobile Development
    • Application Enhancements
    • Application Maintenance
    • Database Modernization

    Speak to an Expert »

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  Join us at the Annual 2008 conference, March 30 - April 3, in Nashville, Tennessee
    Seagull Software:  Web-enable your System i apps with LegaSuite GUI
    VAULT400:  Securely archive data with Instant Back-Up & 24x7 Recovery

    IT Jungle Store Top Book Picks

    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' 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
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    Ricoh Introduces Two Wide Format Printers Mainframe Vendor BluePhoenix Ready to Purchase ASNA

    Leave a Reply Cancel reply

Volume 7, Number 28 -- August 1, 2007
THIS ISSUE SPONSORED BY:

WorksRight Software
Help/Systems
COMMON

Table of Contents

  • Use WDSc to Develop XSL Transformations
  • Have Your Cake and Eat It, Too
  • Admin Alert: Getting Around System i Default Passwords, Part 2

Content archive

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

Recent Posts

  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13
  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12

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