• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Conditional SQL I/O

    June 5, 2013 Ted Holt

    If an RPG programmer has a best friend, it may very well be the CHAIN opcode, which performs a random read on a keyed database file. It’s common programming practice in information-processing shops to read one file sequentially and to chain to read a host of others randomly. SQL handles this sort of thing differently. With SQL, joining is the proper way to retrieve data from two or more tables and/or views. The difference is even more pronounced when some of the random processing may or may not take place.

    Suppose you’re responsible for a database that contains customer orders. Each order is for one item, and the price of an item comes from one of two tables: a standard pricing table (the catalog prices), and a customer-specific pricing table. The usual way to retrieve the price is to look for (randomly read) a customer-specific price first, and if none is found, then and only then look for (randomly read) the standard price. In RPG terms, it’s like this:

    dow '1';
       read order;
       if %eof();
          leave;
       endif;
       // look for a price for this customer & item
       chain (custid: itemid) cusprice;
       if %found();
          SellingPrice = Price;
       else;
          // there was no price for this customer
          // get the catalog price
          chain itemid PriceRec;
          if %found();
             SellingPrice = Price;
          else;
             // there is no price for item
             // perform error routine
          endif;
       endif;
    enddo;
    

    But SQL contains no such conditional logic to control I/O. In an SQL world, the way to handle such a situation is to read everything, whether you need it or not. This is counterintuitive to an old RPG or COBOL programmer, but it works, and it works just fine.

    Take a look at this SQL command:

    select ord.ordernbr, ord.custid, ord.itemid,
           cp.price, prc.price,
           coalesce(cp.price, prc.price) as SellingPrice
      from orders as ord
      left join prices as prc
        on ord.itemid = prc.itemid
      left join cusprices as cp
        on ord.custid = cp.custid
       and ord.itemid = cp.itemid
    

    The primary table, ORDERS, is the one that would typically be read sequentially in an RPG or COBOL program. PRICES (the standard price table) and CUSPRICES (the customer price table) are both included as secondary tables. The conditional logic–giving preference to the customer price over the standard price–is carried out with the COALESCE function.

    COALESCE takes two or more arguments and returns the first one that is not null. In this case, if the customer price (cp.price) is not null, the customer price is the selling price. Otherwise, the standard price (prc.price) is the selling price. The result set illustrates this behavior.

    Order number

    Customer ID

    Item ID

    Customer price

    Standard price

    Selling price

    101

    3

    A1

    null

    1.50

    1.50

    102

    1

    A1

    1.25

    1.50

    1.25

    103

    5

    A1

    null

    1.50

    1.50

    104

    6

    A1

    null

    1.50

    1.50

    105

    8

    A1

    1.10

    1.50

    1.10

    Notice that customer price overrides the standard price for orders 102 and 105.

    Let’s complicate the example a bit.

    Suppose that a customer can belong to another customer (sometimes called a parent or a group). If a customer has negotiated a specific price for an item, that price is to be used in invoicing. If there is no customer-specific price, then the parent’s (group) price is to be used, if such exists. Otherwise, we’re back to the standard price.

    The concept is the same. All necessary tables are joined to form the result set, and COALESCE picks the first non-null of the three values.

    select ord.ordernbr, ord.custid, cus.custgrp,
           ord.itemid, cp.price, gp.price, prc.price,
           coalesce(cp.price, gp.price, prc.price) as UnitPrice
      from orders as ord
      left join prices as prc
        on ord.itemid = prc.itemid
      left join cusprices as cp
        on ord.custid = cp.custid
       and ord.itemid = cp.itemid
      left join customers as cus
        on ord.custid = cus.custid
      left join cusprices as gp
        on cus.cusgrp = gp.custid
    

    Table CUSPRICES is used twice in the query; once for a customer (CP) and once for the customer’s group (GP). Here’s the result set:

    Order number

    Customer ID

    Customer group

    Item ID

    Customer price

    Group price

    Standard price

    Selling price

    101

    3

    0

    A1

    null

    null

    1.50

    1.50

    102

    1

    0

    A1

    1.25

    null

    1.50

    1.25

    103

    5

    2

    A1

    null

    1.35

    1.50

    1.35

    104

    6

    7

    A1

    null

    null

    1.50

    1.50

    105

    8

    2

    A1

    1.10

    1.35

    1.50

    1.10

    Customer price comes from the CUSPRICES row (record) for the customer. Group price comes from the CUSPRICES row for the parent. COALESCE picks the first non-null of the three values, forcing customer price to override the parent price and parent price to override the standard price.

    I’ll mention one more thing, and then I’m done. If this query were from a real production system, I would not include the customer price, group price, and standard price in the result set unless there was a need to do so. The only price I would typically include would be the selling price. I would probably not include the customer group either.

    Order number

    Customer ID

    Item ID

    Selling price

    101

    3

    A1

    1.50

    102

    1

    A1

    1.25

    103

    5

    A1

    1.50

    104

    6

    A1

    1.50

    105

    8

    A1

    1.10

    The fact that I use a column (field) in a query doesn’t mean that I have to put it in the result set.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    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

    Profound Logic Software:  Live Webinar: How to Take IBM i Everywhere with Mobile Apps. June 26
    BCD:  Reduce the IT reporting blues with Monarch for EZ-Pickin's
    RJS Software Systems:  Replace unsupported legacy forms or upgrade to iForms.

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ The Reg: More News From ITJ EIC Timothy Prickett Morgan

    TMW Delivers Web Interface for IBM i Trucking App IBM Rolls Out PureFlex-IBM i Bundle With Decent Discounts

    Leave a Reply Cancel reply

Volume 13, Number 11 -- June 5, 2013
THIS ISSUE SPONSORED BY:

ProData Computer Services
WorksRight Software
System i Developer

Table of Contents

  • When Is A Source Member Not A Source Member?
  • Conditional SQL I/O
  • Admin Alert: Renewing A Default *System Certificate

Content archive

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

Recent Posts

  • To Comfort The Afflicted And Afflict The Comfortable
  • How FalconStor Is Reinventing Itself, And Why IBM Noticed
  • Guru: When Procedure Driven RPG Really Works
  • Vendors Fill In The Gaps With IBM’s New MFA Solution
  • IBM i PTF Guide, Volume 27, Number 27
  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26

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