• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Thinking in Sets

    May 24, 2006 Hey, Ted

    I have a program that contains a complicated SQL command that selects a group of records exactly the way I want them selected. My boss has added a new wrinkle by creating an exceptions file, which would indicate other records that are to be selected, whether they meet the selection criteria or not. I cannot find a way to add the exceptions file without breaking my query. How do I modify my SQL command to handle the exceptions file?

    –Clayton

    You were taught the solution to your problem in elementary school. Do you remember learning about sets in math class? E. F. “Ted” Codd based the whole idea of relational databases on set theory.

    A set is an unordered group of like things. The students in the classroom were a set. The boys in the class were a set. The redheaded boys were a set.

    Your teacher and textbook explained to you that you can relate sets in different ways. For example, overlapping the set of redheaded boys with the set of boys who play baseball gives you the set of redheaded baseball-playing boys. This is known as the intersection of two sets.

    You might combine the two sets into one, in which case the set contains all boys who are redheaded and all boys who like to play baseball. Some of the boys might fit in both categories, but all such boys appear in the result set only once, as there cannot be two instances of one person. This is known as the union of two sets.

    You can also remove the members of one set from another set. For instance, you might remove the redheaded boys from the set of baseball players, which results in the set of baseball players who are not redheaded. Or you could remove the baseball players from the set of redheaded boys, which gives you the set of redheaded boys who do not play baseball. This operation is known as set minus.

    So which of these set operations solves your problem? Union. You need the union of the set of things that pass the rigorous testing of your WHERE clause and the set of things that are to be included whether they satisfy the WHERE clause or not.

    Let me illustrate these set operations with a simpler example. Consider a set of customers, stored in file QIWS/QCUSTCDT.

    R# CUSNUM LSTNAM INIT CITY STATE CDTLMT BALDUE
    1 938472 Henning G K Dallas TX 5000 37
    2 839283 Jones B D Clay NY 400 100
    3 392859 Vine S S Broton VT 700 439
    4 938485 Johnson J A Helen GA 9999 3987.5
    5 397267 Tyron W E Hector NY 1000 0
    6 389572 Stevens K L Denver CO 400 58.75
    7 846283 Alison J S Isle MN 5000 10
    8 475938 Doe J W Sutter CA 700 250
    9 693829 Thomas A N Casper WY 9999 0
    10 593029 Williams E D Dallas TX 200 25
    11 192837 Lee F L Hector NY 700 489.5
    12 583990 Abraham M T Isle MN 9999 500

    Let’s suppose that your query finds customers who owe your company more than 250 simoleons (or whatever your local currency is.)

    SELECT cusnum, lstnam, init, city, state,
           cdtlmt, baldue
      FROM qcustcdt
     WHERE baldue > 250
    

    R# CUSNUM LSTNAM INIT CITY STATE CDTLMT BALDUE
    1 392859 Vine S S Broton VT 700 439
    2 938485 Johnson J A Helen GA 9999 3987.5
    3 192837 Lee F L Hector NY 700 489.5
    4 583990 Abraham M T Isle MN 9999 500

    Now let’s see who’s in the exceptions file.

    SELECT CusNumber
      FROM custexcept
     ORDER BY 1
    
    R# CUSNUMBER
    1 389572
    2 392859
    3 593029

    The only thing lacking is to combine the two sets into one.

    SELECT cusnum, lstnam, init, city, state,
           cdtlmt, baldue
      FROM qcustcdt
     WHERE baldue > 250
    UNION
    SELECT c.cusnum, c.lstnam, c.init,
           c.city, c.state, c.cdtlmt, c.baldue
      FROM custexcept AS e
      JOIN qcustcdt AS c
        ON e.CusNumber = c.cusnum
        ORDER BY 1
    

    R# CUSNUM LSTNAM INIT CITY STATE CDTLMT BALDUE
    1 192837 Lee F L Hector NY 700 489.5
    2 389572 Stevens K L Denver CO 400 58.75
    3 392859 Vine S S Broton VT 700 439
    4 583990 Abraham M T Isle MN 9999 500
    5 593029 Williams E D Dallas TX 200 25
    6 938485 Johnson J A Helen GA 9999 3987.5

    Notice that customer S. S. Vine is included once, not twice. The normal behavior of UNION is to omit duplicate rows. If you do not want to drop duplicate rows, use UNION ALL instead of UNION.

    That takes care of your problem, Clayton. For sake of completeness, I’ll illustrate the other two set operations. These only work at V5R3 and above.

    First, here’s the intersect operation, which returns only the rows that are members of both sets.

    SELECT cusnum, lstnam, init, city, state,   
           cdtlmt, baldue                       
      FROM qcustcdt                        
     WHERE baldue > 250                         
    INTERSECT                                   
    SELECT c.cusnum, c.lstnam, c.init,          
           c.city, c.state, c.cdtlmt, c.baldue  
      FROM custexcept AS e               
      JOIN qcustcdt AS c                   
        ON e.CusNumber = c.cusnum               
        ORDER BY 1                              
    

    R# CUSNUM LSTNAM INIT CITY STATE CDTLMT BALDUE
    3 392859 Vine S S Broton VT 700 439

    Here’s the set minus. You would use this operation if the purpose of the exceptions file were to exclude certain customers.

    SELECT cusnum, lstnam, init, city, state, 
           cdtlmt, baldue                     
      FROM qcustcdt                      
     WHERE baldue > 250                       
    EXCEPT                                    
    SELECT c.cusnum, c.lstnam, c.init,        
           c.city, c.state, c.cdtlmt, c.baldue
      FROM custexcept AS e             
      JOIN qcustcdt AS c                 
        ON e.CusNumber = c.cusnum             
        ORDER BY 1                            
    

    R# CUSNUM LSTNAM INIT CITY STATE CDTLMT BALDUE
    1 192837 Lee F L Hector NY 700 489.5
    4 583990 Abraham M T Isle MN 9999 500
    6 938485 Johnson J A Helen GA 9999 3987.5

    –Ted

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Focal Point Solutions Group

    A CloudSAFE Company

    The Power of Services:
    IBM Cloud and Managed Solutions

    Upgrade your business processes, and save time and resources with specialized, best-in-class IT solutions.

    Managed, Cloud, and Custom Solutions

    Managed Services

    • Infrastructure Monitoring & Management
    • Server Patching
    • Application Patching
    • Managed Backup
    • High Availability/Disaster Recovery Monitoring
    • Cloud Environment Monitoring
    • Office 365 Management
    • Endpoint Management
    • Managed Colocation

    Cloud Infrastructure

    • IBM i Private Cloud
    • IBM AIX Private Cloud
    • VMware Private Cloud
    • VMware Cloud Director
    • Multi-Tenant Cloud
    • Desktop as a Service

    Data Protection & High Availability

    • Disaster Recovery as a Service
    • Backup as a Service
    • IBM i Vaulting

    Security

    • Security Consulting
    • Remote Security Awareness Training & Education
    • Onsite Security Awareness Training & Education
    • Phishing Tests
    • Penetration Tests
    • Mail Security
    • Managed Detection & Response
    • Managed Firewall
    • Endpoint Protection
    • Vulnerability Management
    • Vendor Risk Assessments
    • Security Risk Assessments

    Professional Services

    • Server Virtualization
    • Data & Infrastructure Migrations
    • Hardware & Software Installation
    • Microsoft Office 365 Implementation & Migration
    • Infrastructure Assessments
    • IBM i Consulting
    • IBM AIX Consulting

    Focal Point provides all the tools you need to protect your data, ensure the integrity of your IT infrastructure, and keep your business running.

    Contact Focal Point to Learn More About Our IBM Solutions and Partnerships

    Follow us on LinkedIn

    focalpointsg.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Bug Busters Software Engineering:  Quality software solutions for the iSeries since 1988
    COMMON:  Join us at the Fall 2006 conference, September 17-21, in Miami Beach, Florida
    Xperia:  Fully integrated suite of applications for small- to mid-sized companies

    Lakeview Technology Opens Office in the United Kingdom Server Sales Decline for the Second Straight Quarter

    Leave a Reply Cancel reply

Volume 6, Number 21 -- May 24, 2006
THIS ISSUE SPONSORED BY:

ProData Computer Services
Advanced Systems Concepts
COMMON

Table of Contents

  • Thinking in Sets
  • Present Timestamps in the Local Time Zone
  • Admin Alert: Setting Up Unattended i5 Server IPLs

Content archive

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

Recent Posts

  • IBM i Development and Modernization is Getting A Fresche Start with Some Ground-Breaking Subscriptions
  • CloudSAFE And Focal Point Solutions Group Combine Services, Unify Brands
  • Guru: Partitioning Result Sets Using SQL
  • As I See It: Elusive Connections
  • IBM i PTF Guide, Volume 25, Number 47
  • AWS Inks Deal With Connectria To Have a Power Play
  • IBM i Shops Have Alternatives to Db2 Web Query
  • Eradani Lays Waste to API Payload Restrictions
  • Four Hundred Monitor, November 15
  • Old PHP and Other PASE Apps Break on IBM i 7.5

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