• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Row Value Expressions Simplify Complex Row Selection

    February 10, 2015 Ted Holt

    If you like mile-long, messy, obfuscated, hard-to-read SQL, the kind that makes sane people want to cuss and spit on everybody and everything, then this tip is not for you. And please don’t apply for a job in my shop. But if you have better things to do than debug SQL, then I have a simple but clever technique for you.

    Suppose the chief bean counter walks into your office and asks for a spreadsheet of general ledger transactions. He wants the current-month transactions for accounts 120, 135, 180, 192, and 198. Here’s how a novice would write the query (except that he might not format it quite so prettily):

    select * from glxacts
     where date between '2015-02-01' and '2015-02-28'
       and (account=120 or account=135 or account=180 or
            account=192 or account=198)
    

    And he would get thousands of transactions, which the following few represent:

    SEQ   DEPARTMENT  ACCOUNT  DATE           AMOUNT
      1       22         120   2015-02-01      75.76
      2       22         180   2015-02-01      25.02
      3       34         135   2015-02-01      44.33
      4       34         180   2015-02-01      15.69
      5       34         192   2015-02-01       5.20
      6       46         120   2015-02-01      28.22
      7       46         198   2015-02-01      16.27
      8        1         120   2015-02-01      88.99
     10       22         135   2015-02-01      88.99
     11       34         120   2015-02-01      88.99
     12       46         135   2015-02-01      88.99
    

    But you are not a novice, and for that reason you know that you can simplify the query this way:

    select * from glxacts
     where date between '2015-02-01' and '2015-02-28'
       and account in (120, 135, 180, 192, 198)
    

    The IN operator really cleaned up the code!

    The bean counter needs a refinement of the query. He zeroes in on certain accounts for certain departments. (I’ll keep it to three for simplicity. Assume he wants to know about a lot more.)

    • For department 22, account 180.
    • For department 34, account 135.
    • For department 35, account 198.

    You could do this:

    select * from glxacts
     where date between '2015-02-01' and '2015-02-28'
       and (   department = 22 and account = 180
            or department = 34 and account = 135
            or department = 46 and account = 198  )
    

    That would give you the correct results.

    SEQ   DEPARTMENT  ACCOUNT  DATE           AMOUNT
    ===   ==========  =======  ==========     ======
      2       22         180   2015-02-01      25.02
      3       34         135   2015-02-01      44.33
      7       46         198   2015-02-01      16.27
    

    But doesn’t it seem a shame to have replaced that handy IN operator with a bunch of ugly AND’s and OR’s? I have good news! You can still use IN when comparing two or more fields!

    select * from glxacts
     where date between '2015-02-01' and '2015-02-28'
       and (department, account) in
            (values(22,180), (34,135), (46,198))
    

    Think of the VALUES keyword as a mechanism to create a table on the fly.

    Is that fine, or what!?

    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, Power11, or cloud hosted system, Maxava has you covered!

    Book A Consultation Today

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Northeast User Groups Conference:  25th Annual Conference, March 30 - April 1, Framingham, MA
    Profound Logic Software:  Reach Your Modernization Goals. Register for the February 25 Webinar now!
    System i Developer:  Upgrade your skills at the RPG & DB2 Summit in Dallas, March 17-19

    IBM Layoffs Not As Dramatic As Rumored Orchestrating Your API-Enabled Assets On IBM i

    Leave a Reply Cancel reply

Volume 15, Number 03 -- February 10, 2015
THIS ISSUE SPONSORED BY:

ProData Computer Services
COMMON
WorksRight Software

Table of Contents

  • Create Excel Spreadsheets From DB2 Data With PHPExcel
  • Row Value Expressions Simplify Complex Row Selection
  • Git To It

Content archive

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

Recent Posts

  • 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
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

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