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