Search code examples
sqlnulldb2ibm-midrangedb2-400

How do you get a list of invalid values from a decimal field in Db2?


I have an issue with a column having null values in a not-null decimal field. The issue arises when that column is used (like in a case statement), but not when I simply do a select * from.

The error it comes up with is SQL0802 type 6 - Numeric data that is not valid. I was able to find a few of them and fix it, but I would like to get a list of all of them from this very large table.

But again, any time I use it, it gives me the error. This is what I have been trying mostly:

SELECT *
FROM (
    SELECT keycol_1, keycol_2,
        IFNULL(badcol, -1) AS badcol_tmp
    FROM mytable
) WHERE badcol_tmp = -1

(Note that the column should not have negative numbers, that's why I'm using -1 there). This gives the error listed above.

I have also tried

SELECT *
FROM mytable
WHERE badcol IS NULL

which doesn't give me any results... No error, but no rows. But I have seen it be null from scrolling through all of the data.

How can I get the list of null values in badcol?


Solution

  • The problem isn't that the value is null, if that were the case, then

    SELECT *
    FROM mytable
    WHERE badcol IS NULL
    

    would work. The problem is that the data in the field isn't numeric.

    If you're keeping your system up to date, then congrats! IBM has added some validation utilities in the most recent TR's for 7.3 and 7.4.

    Otherwise try the following (for a 2 digit zoned number):

    select *
    from mytable
    where substr(hex(badcol),1,1) not in ('F','D')
          or substr(hex(badcol),2,1) not between '0' and '9' 
          or substr(hex(badcol),3,1) not in ('F','D')
          or substr(hex(badcol),4,1) not between '0' and '9'