• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Another Reason To Use Unrequired Correlation Names

    September 15, 2015 Ted Holt

    Almost eight years ago I gave you two good reasons to use a correlation name when SQL did not require one. A recent experience revealed to me another good reason to use an unnecessary correlation name, and I am happy to be able to share that information with you today!

    A colleague of mine had an SQL query that ran to completion but produced the wrong results. He asked me to take a look at it, and I’m glad he did, because the experience taught me something. Let me set up the situation for you.

    Assume two tables: a table of customer orders, and a table of invoices. (I’ve omitted most of the columns (fields) such tables would have in order to shorten the code.)

    create table corders
      (  COrderNumber     dec(7),
         CInvoiceNumber   dec(7),
        primary key (COrderNumber))
    
    create table invoices
      (  InvoiceNumber    dec(7),
         InvoiceDate      date,
       primary key (InvoiceNumber))
    
    insert into COrders values
    (1,3),(2,1),(3,4),(4,2),(5,null),
    (6,null),(7,5),(8,null),(9,null),
    (10,null)
    
    insert into invoices values 
    (1,'2015-01-31'), (2,'2015-01-31'),
    (3,'2015-02-01'), (4,'2015-02-02'),
    (5,'2015-02-02')
    

    His query was something like this:

    select * from COrders
      where CInvoiceNumber in
       (select CInvoiceNumber
          from invoices)
    

    Do you see the problem with this statement? If not, why not study it until you do?

    The query runs, and believe it or not, produces the correct results using my test data.

    CORDERNUMBER  CINVOICENUMBER
    ============  ==============
              1              3  
              2              1  
              3              4  
              4              2  
              7              5  
    

    However–and this is a big however–the query gave the correct results by pure coincidence. In my colleague’s case, the result set was obviously wrong.

    In case you didn’t find it, the problem is that the subquery refers to a column–CORDERNUMBER–that is not found in the INVOICES table. SQL allows this, but not without a warning. In the green-screen Start SQL Interactive Session (STRSQL) command, you get this message:

                                 Confirm Statement
    
    You have entered a subquery that contains a correlation
    without qualification for:
      Field. . . . . . . . . . . . .:    CINVOICENUMBER
    
    
    Press Enter to confirm your SELECT statement.
    Press F12=Cancel to return and cancel your SELECT statement.
    

    If you bother to look at the preprocessor listing, an RPG program with embedded SQL produces similarly-worded message SQL0012. But who looks at preprocessor listings when the compilation succeeds? I shamefully admit that I rarely, if ever, do.

    Suppose my friend had used correlation names to qualify the column references, like this:

    select c.*
     from COrders as c
      where c.CInvoiceNumber in
       (select i.CInvoiceNumber
          from invoices as i)
    

    The GUI tool he was using would have heartlessly responded with message SQL0205, Column CINVOICENUMBER, not in table INVOICES in SOMELIB. My colleague would have had no choice but to fix the query.

    Here’s the query as he should have written it, with correlation names.

    select c.*
     from COrders as c
      where c.CInvoiceNumber in
       (select i.InvoiceNumber 
          from invoices as i)
    

    I probably overuse qualification by correlation names, but using unnecessary qualification has never got me into trouble, and I can always find better things to do than debug SQL queries.

    RELATED STORY

    Reasons to Use Unrequired Correlation Names

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions. Upgrading to Power10 or cloud hosted system, Maxava has you covered!

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    ProData Computer Services:  Zero in on the data YOU need NOW with DBU, RDB Connect and SQL/Pro.
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars.
    System i Developer:  Session Grid Posted: RPG & DB2 Summit - Chicago, October 20-22

    Reader Feedback On A Hypothetical Future IBM i System Hacker Defends DEF CON Talk on IBM i Vulns

    Leave a Reply Cancel reply

Volume 15, Number 18 -- September 15, 2015
THIS ISSUE SPONSORED BY:

ProData Computer Services
CCSS
WorksRight Software

Table of Contents

  • A First Look At SQL Descriptors
  • Another Reason To Use Unrequired Correlation Names
  • RCAC In DB2 For i, Part 3: Advanced Topics

Content archive

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

Recent Posts

  • 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
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

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