• 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
    Raz-Lee Security

    With COVID-19 wreaking havoc, cybercriminals are taking advantage of the global impact that it has had on our families, our businesses and our societies. It is more important now than ever to ensure that IT systems are protected, so that when all of this is behind us, we can get back to business as usual as quickly as possible.

    iSecurity Anti-Ransomware protects organizations against ransomware attacks and other kinds of malware that may access and change business-critical data on your IBM i. It even protects against zero-day attacks. Anti-Viruses can only report on the damage an attack has caused, but not stop it.

    iSecurity Anti-Ransomware has been recently enhanced with a Self-Test feature that allows you to simulate a ransomware attack on your IBM i. The simulated attack is limited to the test folder and cannot harm any other folders or files. This new feature lets organizations see how they are protected against known or unknown ransomware.

    Key Features:

    • Real-time scanning for known and unknown ransomware threats.
    • Blocks and disconnects the intruder.
    • Instantaneously sends alerts to SIEM as well as the offending computer.
    • Self-Test for attack simulation
    • Classification of the attack based on log.
    • Automatic updates with the most current ransomware definitions.

    Contact us at https://www.razlee.com/anti-ransomware

    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

  • Power10 Entry Machines: The Power S1024 And Power L1024
  • Thoroughly Modern: Latest IT Trends – Bring Security, Speed, And Consistency To IT With Automation
  • Big Blue Unveils New Scalable VTL For IBM i
  • As I See It: Thank God It’s Thursday
  • IBM i PTF Guide, Volume 24, Number 32
  • JD Edwards Customers Face Support Decisions
  • Security, Automation, and Cloud Top Midrange IT Priorities, Study Says
  • Cleo and SrinSoft in Integration-Modernization Link Up
  • Four Hundred Monitor, August 3
  • IBM i PTF Guide, Volume 24, Number 31

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 © 2022 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.