• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Update One File Based on Another File

    April 14, 2010 Ted Holt

    It’s not uncommon to have to update one file based on data found in another file. In fact, it’s a very common requirement, due to the way that data is “normalized” in relational database management systems. When only one field links the two files–the file that is being updated and the file that contains the search criteria–the SQL syntax is simple. But joining on two or more fields requires more challenging syntax.

    Here’s an example of an update when only one field joins the two files. I add a week to the scheduled ship date of orders of type B customers.

    update salesordh
       set shipdate = shipdate + 7 days
     where customerno in
             (select customerno
                from customers
               where type = 'B')
    

    Since the sales order header file and customer master file share only one common field–customer number–a simple IN expression does the trick.

    But suppose the two files join on two fields–company number and customer number. What then?

    The traditional method is to use the EXISTS construct.

    update salesordh as h
       set shipdate = shipdate + 7 days
     where exists 
       (select *
          from customers as c
         where type = 'B'
           and c.companyno  = h.companyno
           and c.customerno = h.customerno)
    

    EXISTS proves true if the SELECT that follows it returns at least one row (record). For each row of the sales order header file, SALESORDH, the system looks to see if there is at least one type B customer with the same company number and customer number in the customer master file. If at least one row is found, the sales order header record is updated.

    If you are V5R4 or above, you have a simpler option–you can use a row value expression.

    update salesordh
       set shipdate = shipdate + 7 days
     where (companyno, customerno) in
              (select companyno, customerno
                 from customers
                where type = 'B')
    

    Row value expressions let you compare two lists of values to one another. In this example, the company number-customer number combination of a sales record are compared to the set of all company number-customer order combinations from the type B customers.

    Thanks to row value expressions, I have jettisoned the non-intuitive EXISTS and returned to the simple IN of the first example!



                         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
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    BCD:  April 21 Webinar - Web enable your IBM i programs & satisfy end-users
    inFORM Decisions:  Paperless saves $$$. Learn more and get FREE white papers.
    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

    Systems Director Express for X86 Keeps an Eye on Heat and Power Usage IBM’s Power7 Blades Pack a CPW Punch

    Leave a Reply Cancel reply

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

SEQUEL Software
WorksRight Software
Halcyon Software

Table of Contents

  • Query Control
  • Update One File Based on Another File
  • Admin Alert: Some Simple Ideas for Getting the Best System i Lease

Content archive

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

Recent Posts

  • Power11 Entry Machines: The Power S1124 And Power L1124
  • BRMS Isn’t The Only Backup Product With A Security Problem
  • Guru: A Faster Way To Sign A JWT
  • Maxis Adds IBM i Support To Database Modernization Tool
  • IBM i PTF Guide, Volume 27, Number 28
  • To Comfort The Afflicted And Afflict The Comfortable
  • How FalconStor Is Reinventing Itself, And Why IBM Noticed
  • Guru: When Procedure Driven RPG Really Works
  • Vendors Fill In The Gaps With IBM’s New MFA Solution
  • IBM i PTF Guide, Volume 27, Number 27

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