• 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
    Precisely

    Participate in Precisely’s 2021 IBM i Security Survey

    Every year, Precisely asks IT pros responsible for IBM i security about their top challenges, strategies, technologies and best practices. These annual survey results provide a revealing look at the current state of IBM i security through the eyes of your peers.

    Click here to contribute your perspectives to this year’s survey.

    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

    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 Software And Power Systems Upgrades Keep Rolling Forward
  • Preparing For What’s Next In A Thoughtful, Structured Way
  • Guru: Fall Brings New RPG Features, Part 3
  • ARCAD Plugs IBM i DevOps Suite Into Microsoft Azure
  • Park Place Buys Curvature To Become Maintenance Goliath
  • 2021 Predictions for IBM i, Part 1
  • West Four Stands Out With On Demand Color Label Printing
  • HelpSystems Acquires Data Security, File Transfer Companies
  • Four Hundred Monitor, January 13
  • IBM i PTF Guide, Volume 23, Number 2

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

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.