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.
|
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.