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


/* 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
/* 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(
land( x'00FF00FF00FF00FF'
, substr( hex(Zcol) , 1, 8 ) )
, x'00', ''
, ' ', '1234567890'
<> ''


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

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].