Re: Finding invalid data in zoned numeric fields with SQL

[CRPence <CRPbottle@xxxxxxxxx>]

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