SQL and Invalid Decimal Data

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