Re: Finding invalid data in zoned numeric fields with SQL

[Terrence Enger <dk122@xxxxxxxxxxx>]

On Mon, 2009-07-13 at 14:55 -0500, Elvis Budimlic wrote:
| Not overly ugly for a one time thing... perhaps:
|
| select rrn(a),hex(myField), f1 from myLib/myFile a
| where substring(hex(myField), 1,1) <> 'F' and
|       substring(hex(myField), 3,1) <> 'F' and
|       substring(hex(myField), 5,1) <> 'F' and
|       substring(hex(myField), 7,1) <> 'F' and
|       substring(hex(myField), 9,1) <> 'F' and
|       substring(hex(myField),11,1) <> 'F' and
|       substring(hex(myField),13,1) <> 'F' and
|       substring(hex(myField),15,1) not in ('F','D')
|
| or
|
| select rrn(a),hex(myField), f1 from myLib/myFile a
| where 'F' not in
|       (substring(hex(myField), 1,1),
|        substring(hex(myField), 3,1),
|        substring(hex(myField), 5,1),
|        substring(hex(myField), 7,1),
|        substring(hex(myField), 9,1),
|        substring(hex(myField),11,1),
|        substring(hex(myField),13,1)) AND
|        substring(hex(myField),15,1) not in ('F','D')
|
| Hth, Elvis

Hmm.

I think those statements would omit rows with any valid zone
digit in the field.  It would be safer to omit rows with all
valid zone digits.  Unless, of course, I have been staring at
this screen too long today.  Which very well could be the case.

So, just off the top of my head, I suggest changing the ANDs in
the first statement or ORs, or ...

    select rrn(a), hex( myField ), f1
      from myLib/myFile
     where substring( hex( myField ),  1, 1 )  ||
           substring( hex( myField ),  3, 1 )  ||
           substring( hex( myField ),  5, 1 )  ||
           substring( hex( myField ),  7, 1 )  ||
           substring( hex( myField ),  9, 1 )  ||
           substring( hex( myField ), 11, 1 )  ||
           substring( hex( myField ), 13, 1 )  ||
           substring( hex( myField ), 15, 1 )  ||
           not in ( 'FFFFFFFF', 'FFFFFFFD' )

Validation of the digit digits is left as an exercies for the
reader <grin />.

HTH,
Terry.