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?
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'