• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Multiformat SQL Data Sets

    April 30, 2008 Hey, Ted

    DDS-defined logical files can have multiple record formats, each one of them coming from different physical files of different types of data. I would like to do the same sort of thing in SQL. That is, I want to retrieve all the records from one file followed by all the records from a second file, grouped by one or more common key fields. This is not a join, and it doesn’t seem like a union either, because the two data sets are so different. Am I trying to do the impossible?

    –David

    What you’re doing may be unusual, but it’s not unrealistic. You’re right that you don’t need a join. Despite the difference in the two types of data, you need a union. Since both tables have columns that have no counterpart in the other one, you’ll need to include nulls or default values as placeholders. I can explain this with a simple example.

    Let’s say that we have a table of items that customers have ordered from us.

    create table orders
       (Order  dec(5),          
        Item   char(4),         
        Qty    dec(3))     
         
    insert into orders values
      (201, 'A946',  5),       
      (203, 'B212',  7),       
      (207, 'A104', 12),       
      (210, 'B212',  4),       
      (211, 'B212',  4)        
    

    We have another table that shows where items are stored in the warehouse.

    create table inventory
       (Item      char(4),         
        Location  char(5),         
        Qty       dec(3))          
    
    insert into inventory values
       ('A104', '1103B', 20),     
       ('A104', '1412B',  6),     
       ('A726', '0902A',  4),     
       ('B212', '0312C',  8),     
       ('B212', '0404B',  8),     
       ('B212', '0411C',  6)      
    

    Here’s how we might combine the data into one set.

    select ord.item, 'A' as ID,                
           ord.order, ord.qty as OrdQty,       
           cast(null as char(5)) as Loc,       
           cast(null as dec(3)) as LocQty      
      from orders as ord
    union all
    select inv.item, 'B', cast(null as dec(5)),
           cast(null as dec(3)),               
           inv.location, inv.qty               
      from inventory as inv                  
    order by 1,2,3,5
    

    Now take a look at the result, and then I’ll explain the query in a bit more detail.

    ITEM  ID   ORDER   ORDQTY  LOC    LOCQTY
    ====  ==   =====   ======  =====  ======
    A104  A      207       12  -          -
    A104  B        -        -  1103B     20 
    A104  B        -        -  1412B      6 
    A726  B        -        -  0902A      4 
    A946  A      201       5   -          -
    B212  A      203       7   -          -
    B212  A      210       4   -          -
    B212  A      211       4   -          -
    B212  B        -       -   0312C      8 
    B212  B        -       -   0404B      8 
    B212  B        -       -   0411C      6
    

    The first SELECT (shown below) retrieves order information. Notice that the location and location quantity columns are left null, since they don’t exist in the orders. Also notice that I have forced an “A” into the second column, in order to sort the data and also to identify the row as an order row.

    select ord.item, 'A' as ID,                
           ord.order, ord.qty as OrdQty,       
           cast(null as char(5)) as Loc,       
           cast(null as dec(3)) as LocQty      
      from orders as ord                     
    

    The second SELECT retrieves inventory information. Notice that the order fields are loaded with nulls.

    select inv.item, 'B', cast(null as dec(5)),
           cast(null as dec(3)),               
           inv.location, inv.qty               
      from inventory as inv                  
    

    The second column identifies each type of record and aids in sorting the data properly. I forced an “A” into order records and a “B” into inventory records. If I were to embed this statement in an RPG program, for example, my program could use this field to identify the type of data in a row.

    If you don’t want to deal with nulls, you can use blanks and zeros instead, as the following query illustrates.

    select ord.item, 'A' as ID,           
           ord.order, ord.qty as OrdQty,  
           ' ' as Loc,                    
           0 as LocQty                    
      from orders as ord                
    union all                             
    select inv.item, 'B', 0, 0,           
           inv.location, inv.qty          
      from inventory as inv             
    order by 1,2,3,5
    
    ITEM  ID   ORDER   ORDQTY   LOC   LOCQTY
    ====  ==   =====   ======  =====  ======
    A104  A      207       12              0
    A104  B        0        0  1103B      20
    A104  B        0        0  1412B       6
    A726  B        0        0  0902A       4
    A946  A      201        5              0
    B212  A      203        7              0
    B212  A      210        4              0
    B212  A      211        4              0
    B212  B        0        0  0312C       8
    B212  B        0        0  0404B       8
    B212  B        0        0  0411C       6
    

    –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
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    ARCAD Software:  Register now for May 21 Practical Test Automation Webinar
    LANSA:  It's Time for 4 days of education at the LANSA User Conference, May 4 – 7, in Orlando
    Vision Solutions:  A Rewind Button for i5 Data? Read the Whitepaper

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.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 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

    Decline In Vulnerabilities Belies Threat Increase, Microsoft Says in New Security Report IBM Previews “Blue Business” SMB System Sales Approach

    Leave a Reply Cancel reply

Volume 8, Number 17 -- April 30, 2008
THIS ISSUE SPONSORED BY:

ProData Computer Services
WorksRight Software
Guild Companies

Table of Contents

  • Multiformat SQL Data Sets
  • Build Pivot Tables over DB2 Data
  • Solve a Client Access Mystery, Win a No Prize

Content archive

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

Recent Posts

  • 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
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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