Re: Finding invalid data in zoned numeric fields with SQL

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.