• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL and Invalid Decimal Data

    August 7, 2013 Ted Holt

    I’m sure no reader of this august publication likes decimal data errors. One single such error can ruin an entire day. The wise programmer uses tools and techniques to keep invalid numeric data from becoming a problem. One such tool that you can use to find and fix invalid data is SQL.

    Assume that a physical file has a customer account number stored as seven digits packed decimal, with no decimal positions. Suppose there are one or more blank records in the physical file. You query the file by customer number, like this:

    select * from baddata
    where customerid = 2026025
    

    The system heartlessly responds with error messages: QRY2283: Selection error involving field CUSTOMERID; and CPD4019: Select or omit error on field BADDATA_1.CUSTOMERID member BADDATA.

    So what’s an i professional to do?

    The solution is to use the HEX function to select the bad records. Since a blank is X’40’ in the EBCDIC collating sequence, this is the SQL command you need.

    select * from baddata
    where hex(customerid) = '40404040'
    

    Once you find the bad data, you can delete it.

    delete   from baddata
    where hex(customerid) = '40404040'
    

    Or, if you prefer, you can correct it.

    update   baddata
    set customerid = 0
    where hex(customerid) = '40404040'
    

    Blanks may be the most common form of invalid decimal data, but they are not the only invalid values. Even so, the HEX function is still the way to find and fix the data. Here’s another example.

    COST is a five-digit, packed-decimal field with two decimal positions. This statement retrieves items that have invalid cost values.

    select i.ItemNumber, i.Description
      from items as i
     where substr(hex(i.cost),1,1) < '0'
        or substr(hex(i.cost),2,1) < '0'
        or substr(hex(i.cost),3,1) < '0'
        or substr(hex(i.cost),4,1) < '0'
        or substr(hex(i.cost),5,1) < '0'
        or substr(hex(i.cost),6,1) not between 'A' and 'F'
    

    This statement will find items with any non-packed item cost, including blanks.

    These illustrations use packed-decimal data. The principle is the same for zoned-decimal data.



                         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

    Maxava:  FREE Webinar: Test your DR without Downtime. September 12
    System i Developer:  Upgrade your skills at the RPG & DB2 Summit in Minneapolis, Oct 15-17.
    Linoma Software:  GoAnywhere Secure File Transfer. Simplify. Automate. Encrypt.

    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

    Fall COMMON Conference Just One Month Away IBM Forms OpenPower Consortium, Breathes New Life Into Power

    One thought on “SQL and Invalid Decimal Data”

    • Dan says:
      July 30, 2021 at 10:38 am

      I like this solution since I needed to find and fix a numeric field in one of my customers old S36 file. In my case I don’t want to delete the record I have a Salesman number in the Customer file that I want to run SQL on. However there are records that have Blanks in the numeric field (hex ‘4040’). In the past I wrote an RPG to update the field so I could then use SQL. Then I wondered if I could use SQL to fix the bad records since your solution allows me to find them. Turns out you can if you know how the field is stored.
      In my case the field (INSSLS) is 2.0 Signed field, so I need the hex to be ‘F0F0’.
      So I did this.
      UPDATE qs36f/custmast
      SET INSSLS = X’F0F0′
      WHERE HEX(INSSLS) = ‘4040’

      So by assigning the Hex value of X’F0F0′ I was able to fix the bad records.

      Lets say (INSSLS) is 3.0 Packed field then I would have used X’000F’

      Not going to try and teach how numeric data is stored but knowing that you can use SQL to update a value with Hex is actually a big deal for me. Could be I’m one of the last to know this.

      Reply

    Leave a Reply Cancel reply

Volume 13, Number 15 -- August 7, 2013
THIS ISSUE SPONSORED BY:

PowerTech
WorksRight Software
Profound Logic Software

Table of Contents

  • IFSPOP–Another (And A Better) IFS Interface
  • SQL and Invalid Decimal Data
  • Admin Alert: Budgeting For Your Next Power Systems Upgrade

Content archive

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

Recent Posts

  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12
  • Unattended IBM i Operations Continue Upward Climb
  • VS Code Is The Full Stack IDE For IBM i
  • Domino Runs on IBM i 7.5, But HCL Still Working on Power10
  • Four Hundred Monitor, March 6
  • IBM i PTF Guide, Volume 25, Number 11

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 © 2023 IT Jungle