• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Using Lateral Correlation To Define Expressions In DB2 For i

    August 2, 2016 Michael Sansoterra

    The SQL implementation in DB2 for i is second to none. However, one irritating thing common to various SQL dialects is the need to repeat expressions in query. As SQL matured over the years, techniques such as nested table and common table expressions became available to, among other things, reduce repetitive expressions. This tip illustrates the use of the LATERAL correlation as another way to avoid repetition.

    The Problem

    Say you’re writing a report for a grocery wholesaler, where markup on food items is small and discounts are even smaller. Looking for orders that were not priced correctly, you’re tasked with writing a query that reports sales order detail lines based on two criteria:

    1. Products with an overall discounted price >$10
    2. The current list price < discounted line price

    The report should be sorted with the biggest discounts first.

    In the old days, your query might have looked like this:

    SELECT SOD.*,
           OrderQty*UnitPrice AS ExtPrice,
           OrderQty*UnitPrice*(1-UnitPriceDiscount) AS DiscountPrice,
           p.Name,
           p.ListPrice
      FROM SalesOrderDetail SOD
      JOIN Product p ON p.ProductId=sod.ProductId 
                    AND p.ListPrice<OrderQty*UnitPrice*(1-UnitPriceDiscount)
     WHERE OrderQty*UnitPrice-OrderQty*UnitPrice*(1-UnitPriceDiscount) >10.00
    ORDER BY OrderQty*UnitPrice-OrderQty*UnitPrice*(1-UnitPriceDiscount) DESC
    

    Notice that ExtPrice expression (in blue) is repeated three times in the query and the DiscountPrice expression appears four times. A query like this can be difficult to maintain.

    The Solutions

    No matter, a common table expression (CTE), can alleviate the need for all that repetition so that the expressions are defined once and referenced by name everywhere else within the query:

    WITH OrderData AS (
    SELECT SOD.*,
           OrderQty*UnitPrice AS ExtPrice,
           OrderQty*UnitPrice*(1-UnitPriceDiscount) AS DiscountPrice
      FROM SalesOrderDetail SOD
    )
    SELECT od.*,
           p.Name,
           p.ListPrice
      FROM OrderData od
      JOIN Product p ON p.ProductId=od.ProductId 
                    AND p.ListPrice<od.DiscountPrice
     WHERE ExtPrice-DiscountPrice>10.00
    ORDER BY ExtPrice-DiscountPrice DESC
    

    Likewise, a nested table expression is probably a little more concise for this simple query and only requires the expressions to be written once:

    SELECT SOD.*,
           p.Name,
           p.ListPrice
      FROM (
    SELECT SalesOrderDetail.*,
           OrderQty*UnitPrice AS ExtPrice,
           OrderQty*UnitPrice*(1-UnitPriceDiscount)  AS DiscountPrice
      FROM SalesOrderDetail) SOD
      JOIN Product p ON p.ProductId=sod.ProductId 
                    AND p.ListPrice<sod.DiscountPrice
     WHERE ExtPrice-DiscountPrice>10.00
    ORDER BY ExtPrice-DiscountPrice DESC
    

    A lesser known coding alternative, is to use LATERAL correlation as a way to define expressions in such a way that they can be referenced by name throughout the rest of the query:

    SELECT SOD.*,
           calcs.ExtPrice,
           calcs.DiscountPrice
      FROM SalesOrderDetail sod
    CROSS JOIN LATERAL (
        VALUES(sod.OrderQty*sod.UnitPrice,
               sod.OrderQty*sod.UnitPrice*(1-sod.UnitPriceDiscount)
    )) calcs (ExtPrice,DiscountPrice)
      JOIN Product p ON p.ProductId=sod.ProductId 
                    AND p.ListPrice<calcs.DiscountPrice
     WHERE ExtPrice-DiscountPrice>10.00
    ORDER BY ExtPrice-DiscountPrice DESC
    

    Recall that the significant behavior with LATERAL correlation is that it allows references to columns from tables defined earlier in the query. In the above example, a VALUES clause is used to construct one row consisting of the two expressions: ExtPrice and DiscountPrice. LATERAL allows the VALUES clause to reference columns from the SalesOrderDetail table. This row is CROSS JOINed to the SalesOrderDetail table (hence an extension of the SalesOrderDetail row). Thereafter the expression aliases can be referenced in the remainder of the query.

    Which technique to use is up to you. In this instance, the performance of the CTE and LATERAL SQL statements are equivalent. For this simple demonstration, where the expressions come from values within a single table, my preference is to use the nested table expression.

    However, in more complex scenarios such as where expressions are based on values from multiple tables, I’m starting to like the LATERAL technique because it allows me to continue writing the query in the normal “top down” manner without having to be interrupted to recraft the statement as a CTE or nested table expression. I like CTEs but when they get large it’s a pain reading the statement as your eyes are forced to bounce back and forth between the CTE definition(s) and the final portion of the query.

    LATERAL correlation may not be the most widely used technique by DB2 devs, but it’s worthwhile to know as it may come in handy.

    Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    System i Developer:  RPG & DB2 Summit - October 4-6 2016 in Chicago. Register now!
    COMMON:  Open Source and Systems Management at the COMMON Forum. August 24-25 in Chicago.
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    Systems Monitoring Made Easier, Better, GUI-er IBM i Fundamental Strategy Unchanged, Always Changing

    3 thoughts on “Using Lateral Correlation To Define Expressions In DB2 For i”

    • SQL "LATERAL JOIN" ... questo sconosciuto! - Faq400.com says:
      April 29, 2018 at 8:19 am

      […] Puoi trovare altri esempi di LATERAL JOIN ai seguenti link: Nick Litten – "Converting RGP to Set Based SQL" IT Jungle – "Usining lateral correlation to define expressions in DB2 for i" […]

      Reply
    • SQL "Lateral Join" ... did you know it? - Faq400.com says:
      April 29, 2018 at 2:45 pm

      […] Here are some interesting links: Nick Litten – "Converting RGP to Set Based SQL" IT Jungle – "Usining lateral correlation to define expressions in DB2 for i" […]

      Reply
    • SQL "Lateral Join" ... do you know LATERAL JOIN? - Faq400.com says:
      April 29, 2018 at 2:46 pm

      […] Here are some interesting links: Nick Litten – "Converting RGP to Set Based SQL" IT Jungle – "Usining lateral correlation to define expressions in DB2 for i" […]

      Reply

    Leave a Reply Cancel reply

Volume 16, Number 17 -- August 2, 2016
THIS ISSUE SPONSORED BY:

WorksRight Software
T.L. Ashford
System i Developer

Table of Contents

  • CHAIN vs SELECT INTO
  • Give Me Fewer (Not More!) Parameters, Please!
  • Using Lateral Correlation To Define Expressions In DB2 For i

Content archive

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

Recent Posts

  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19
  • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
  • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
  • i-Rays Brings Observability To IBM i Performance Problems
  • Another Non-TR “Technology Refresh” Happens With IBM i TR6
  • IBM i PTF Guide, Volume 27, Number 18

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