• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Conditional SQL Unions

    July 17, 2017 Ted Holt

    SQL unions combine two or more result sets into one. That’s what they were designed to do. But unions also provide a way to choose between alternate result sets, i.e. to enable or disable SELECT statements at run time. I have used this feature to advantage on numerous occasions. Here’s how it’s done.

    First consider the nature of unions. Each result set of a union can return data or no data, depending on the criteria in the WHERE and HAVING clauses. If a SELECT retrieves no data, the system appends an empty set to the union result. The way to turn a SELECT on or off, then, is to control the selection criteria in the WHERE and HAVING clauses.

    As always, we need an example. Let’s start with a simple query. Let’s make a list of customers in Minnesota and New York. This would be a very easy query.

    select State, City, CusNum, LstNam
      from qiws.qcustcdt
     where State in ('MN', 'NY')
    

    If we have customers in either or both of those states, we get a result set.

    State City CusNum LstNam
    MN Isle 846283 Alison
    MN Isle 583990 Abraham
    NY Clay 839283 Jones
    NY Hector 397267 Tyron
    NY Hector 192837 Lee

    If we have no customers in those states, then no rows (records) match the WHERE clause and we get an empty set.

    Suppose that the result set, whether empty or populated, loads a grid in a GUI form. If the result set contains data, the user gets a list of customers. If the result set is empty, let’s be considerate and give the user a message. We don’t want people to sit around wondering if the computer is ever going to process their request or not.

    with Selected as
       (select State, City, char(cusnum) as CusNum, LstNam
          from qiws.qcustcdt
         where State in ('MN', 'NY'))
    select State, City, CusNum, LstNam
      from (select State, City, CusNum, LstNam
              from Selected     
             union all
            select 'No data', ' ', ' ', ' '
              from Selected
            having count(*) = 0 ) as x (State, City, CusNum, LstNam)
    order by State
    

    Look at the difference. I moved the SELECT into a common table expression called Selected.

    with Selected as
       (select State, City, char(cusnum) as CusNum, LstNam
          from qiws.qcustcdt
         where State in ('MN', 'NY'))
    

    I chose to convert the number account number to character. That’s not necessary, but it will make the union a little smoother.

    Now query the common table expression:

    select State, City, CusNum, LstNam
      from (select State, City, CusNum, LstNam
              from Selected     
             union all
            select 'No data', ' ', ' ', ' '
              from Selected
            having count(*) = 0 ) as x (State, City, CusNum, LstNam)
    order by State
    

    The FROM clause unions two SELECT statements. The first reads the data in Selected, and so may or may not return a result set. The second one returns a result set of one row if the common table expression is empty.

    select 'No data', ' ', ' ', ' '
      from Selected
    having count(*) = 0
    

    This statement may look a little funny to you. It has a HAVING clause without a GROUP BY. In such a case, the entire input set (here, common table expression Selected) is treated as one group, and the SELECT clause can only have literals and aggregate functions. If it bothers you that GROUP BY is missing, you can add this one:

    group by ()
    

    If the result set comes up empty, the grid shows this:

    State City CusNum LstNam
    No data

    The effect is that one, and only one, of the two SELECT statements returns data.

    Works for me.

    RELATED STORIES

    The State of the UNION

    Grouping a Union

    Using UNION for Data Analysis

    SQL Facts Of UNION And ORDER BY

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, IBM i, SQL

    Sponsored by
    Fresche Solutions

    Executive Report for IT Leaders

    Why Smart Modernization Is the Path to Successful IBM i Digital Transformation

    Download this report to get answers these key questions:

    • What is driving digital transformation?
    • Where are organizations going wrong with respect to modernization?
    • What is the best / most efficient / smartest way forward?

    Get the Report »

    _________________________________________________________________

    About Fresche Solutions

    Fresche is the leading provider of digital transformation enablement, application modernization and optimization solutions for companies that rely on IBM i Power Systems. Through the power of automated tools and experienced professionals, Fresche delivers smart solutions that bring our clients the best, boldest ideas to fuel transformation and better manage their business and IT. Our low-risk, phased approach is giving companies new ability to digitally innovate and improve operational processes while leveraging IT assets so clients can take advantage of digital solutions including web, mobile, cloud and AI.

    We can help you with:

    • Developing your IT strategy
    • Building an IBM i roadmap
    • Transforming your RPG and CA 2E (Synon) code to modern languages
    • Addressing developing shortages
    • Quickly onboarding new IT staff
    • Increasing developer productivity
    • Integrating with other systems
    • Extending your ERP
    • Understanding your applications
    • Deploying modern interfaces
    • Improving business agility
    • Starting your digital transformation journey

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    IBM i Security Expert Interprets PCI and Multi-Factor Authentication Of Course i’s Not The AS/400

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 47

This Issue Sponsored By

  • BCD
  • Profound Logic Software
  • OCEAN Tech Con 2017
  • Maxava
  • Manta Technologies

Table of Contents

  • Getting Hyperconverged On Power Systems
  • Of Course i’s Not The AS/400
  • Guru: Conditional SQL Unions
  • IBM i Security Expert Interprets PCI and Multi-Factor Authentication
  • Mixed Results For IT Spending Forecasts

Content archive

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

Recent Posts

  • Query Supervisor Gives Database Engineers New Power
  • IBM Unveils New and Improved IBM i Services
  • 3 Takeaways from the 2021 PowerTech Security Report
  • Four Hundred Monitor, April 14
  • IBM i PTF Guide, Volume 23, Number 15
  • Big Blue Unveils Spring 2021 IBM i Technology Refreshes
  • Thoroughly Modern: Innovative And Realistic Approaches To IBM i Modernization
  • Guru: Web Services, DATA-INTO and DATA-GEN, Part 2
  • Back To The Future With A New IBM i Logo
  • IBM i PTF Guide, Volume 23, Number 14

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

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.