Numeric field with Invalid data

[paolinosal]

Hi all,
I've a file created with DDS, that contains some invalid numeric data in numeric field (es. blank in numeric column).

Is there a way to identify this records and force zero with an update?

Many thanks.

[jtaylor___]

IMO, that's the #1 reason to not use DDS PFs.

My fix is always to use RPG. Read every row, checking for decimal data errors. On error, clear the field and update.

[arrow483]

I have never found a good way to find these. Be aware this can be an implict cast as well. For example if you compare Order no (numeric) to a another value that is char, an implict cast occurs to make the compare work. Why it can't at least give a field name I don't know.
jtaylor's method works if the file has bad data, but does not find where cast or where clause has a compare issue.

[Peder Udesen]

You could use the control specification keyword
FIXNBR(*ZONED *INPUTPACKED)
in a program to fix the error.

But if this is a reoccurring error I would locate the program(s) that causes the error.

Regards
Peder

[B.Hauser]

SQL cannot handle invalid data in numeric values. Because SQL assumes the data is correct. BTW it is not possible to enter invalid numeric data in SQL tables (not even if you try a CPYF with *NOCHK).
If you know there are blanks in numeric fields, you may use a circumvent with SQL, i.e. convert the numeric column into hex an then search through the hex value for blanks.

Select RRN(a), Hex(ColumnX)
from YourTable a
Where Hex(ColumnX) like '40%';

You also could use regular expressions for seaching the Hex Value.

Birgitta

[paolinosal]

SQL cannot handle invalid data in numeric values. Because SQL assumes the data is correct. BTW it is not possible to enter invalid numeric data in SQL tables (not even if you try a CPYF with *NOCHK).
If you know there are blanks in numeric fields, you may use a circumvent with SQL, i.e. convert the numeric column into hex an then search through the hex value for blanks.

Select RRN(a), Hex(ColumnX)
from YourTable a
Where Hex(ColumnX) like '40%';

You also could use regular expressions for seaching the Hex Value.

Birgitta

Hi all,
with HEX it works.
Many thanks.

Bye