Re: Finding invalid data in zoned numeric fields with SQL

Jim Essinger wrote:

I have a file that has bad data showing up in a field. The file
was created by a third party software vendor, and while we wait
for them to find and fix the problem, I need to find a way to
identify those records with invalid data, and replace that data
with zeros. I would like to do this with SQL if I can, but I
don't know how to test a numeric field for the hex value of "40404040" or blanks. Better yet would be to test "Not numeric"
(I know that COBOL can do that test!).

Is there a way to test if a numeric field contains something
other than a number in SQL?

FWiW the following SQL was tested [only a retyped version was validated; no copy\paste was available] using a four byte zoned decimal numeric column, and the SELECT returned only the examples of decimal data errors. Presumably this is no longer of any use for the original request[er], but possibly of interest to someone for an example on the technique & scalar functionss used. So, for the archives...

<code>

/* given: Zcol NUMERIC (4, 0) & SRTSEQ(*HEX) */
/* select rows where Zcol has invalid zone decimal data */
/* include relative row number for reference */
/* include hex view of raw data for reference */
/* include Zcol [may show as '+'s in STRSQL report] */
/* include other columns; e.g. for [unique] key of row */

/* 013b x'F0F1F382' bad sign */
/* x'C6F0C6F1C6F3F8F2' */
/* 01c2 x'F0F183F2' bad zone */
/* x'C6F0C6F1F8F3C6F2' */
/* 01Ü2 x'F0F1FBF2' bad digit */
/* x'C6F0C6F1F5C2C6F2' */

select rrn(a), hex(Zcol), Zcol, a.*
from TheFile a
where
/* test zone portion of the sign digit */
( substr( hex(Zcol) , 7, 1 )
not between 'A' and 'F'
)
/* test zone portion of all non-sign digits */
or ( land( x'C600C600C600'
, substr( hex(Zcol) , 1, 6 ) )
<> x'C600C600C600'
)
/* test digit portion of all digits */
or ( translate(
replace(
land( x'00FF00FF00FF00FF'
, substr( hex(Zcol) , 1, 8 ) )
, x'00', ''
)
, ' ', '1234567890'
)
<> ''
)

</code>

To make the above request more generic, instead of specific to a four digit zoned decimal, the substrings could be adjusted to have the length be a derivation from the length of either the Zcol or hex(Zcol), and the hex literals for the "logical and" [land] could be generated with REPEAT() characters using similar derivations from the length of the field; e.g. the literal six can be replaced by the expression length(hex(Zcol))-2 and the literal x'C600C600C600' could be replaced by the expression repeat(x'C600',length(hex(Zcol))/2-1) or similarly repeat(x'C600',length(Zcol)-1).

Note that the interactive SQL feature did not diagnose [with plus symbols] any examples whereby only non-sign zone portions of the number were not valid; i.e. were not 0xF. At one time Query/400 would have diagnosed those conditions, but even that feature seems no longer to do so; though they are the same report writer, only the Query/400 asks\asked the database to "force conversion mapping" of the numeric data to ensure numeric validation is done by the LIC DB.

Regards, Chuck