• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • How Do I Find What’s Not There?

    October 14, 2009 Hey, Ted

    Our inventory items are assigned seven-digit numbers. When we accept an order for a custom product, we assign it a number, build the product, ship it, and retire the number. After a few years, the system automatically purges these numbers, which means we are free to reuse these numbers. Is there a way to make an SQL query find the numbers within a range that are not assigned to items?

    –Tim

    I’ll give you a method that works at V5R4 or above, Tim.

    Let’s say your database file is IBM‘s famous QIWS/QCUSTCDT, and that you want to find available customer numbers in the range 938470 through 938479. There is only one customer in that range–G. K. Henning, who has been assigned the number 938472–so we should get a list of nine available numbers.

    Here’s the query.

    with list (Number) as
    (select 938470
       from qsys2/qsqptabl
      union all
     select Number+1
       from list
      where Number < 938479)
    select Number from list
      where Number not in
           (select cusnum
              from qiws/qcustcdt)
    order by 1
    

    Now let’s tear this apart.

    The recursive common table expression, list, builds a temporary table of all the whole numbers in the range 938470 through 938479.

    Following the common table expression, the main select retrieves all numbers in list that are not found as customer numbers in QCUSTCDT.

    Voilà!

    –Ted



                         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
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Vision Solutions:  Free software for measuring overall i5/OS system health
    Manta Technologies:  Fall Sale on i training courses! Order by October 15 and SAVE 25%
    S4i Systems:  Express manages System i, mainframe, and any LINUX, UNIX, AIX document files

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Sparxent Issues First Update of XAware Data Integration Tool Gartner Ranks the Managed File Transfer Products

    Leave a Reply Cancel reply

Volume 9, Number 32 -- October 14, 2009
THIS ISSUE SPONSORED BY:

Help/Systems
Profound Logic Software
Manta Technologies

Table of Contents

  • Getting the Message, Part 1
  • How Do I Find What’s Not There?
  • Admin Alert: Locking Down i5/OS System Security Values

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