RE: Finding invalid data in zoned numeric fields with SQL

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

Jim,

The procedure in the previous email shown below assumes a packed field, not
a zoned field. Taking the HEX() of a valid packed field gives you all
numeric digits followed by the sign nibble. Checking for the sign nibble
would be required. Otherwise a blank field (hex = 40404040) would be seen as
valid.

Taking the HEX() of a valid zoned field gives you "F" in every other
character position except for the next to last one for negative numbers.

This might be extreme, but it should work. I'm assuming a 7-digit zoned
field.

UPDATE MYTABLE
SET MYFIELD = 0
WHERE SUBSTR(HEX(MYFIELD),1,1)||SUBSTR(HEX(MYFIELD),3,1)||          
      SUBSTR(HEX(MYFIELD),5,1)||SUBSTR(HEX(MYFIELD),7,1)||          
      SUBSTR(HEX(MYFIELD),9,1)||SUBSTR(HEX(MYFIELD),11,1)<>'FFFFFF' 
OR TRANSLATE(
      SUBSTR(HEX(MYFIELD),2,1)||SUBSTR(HEX(MYFIELD),4,1)||          
      SUBSTR(HEX(MYFIELD),6,1)||SUBSTR(HEX(MYFIELD),8,1)||          
      SUBSTR(HEX(MYFIELD),10,1)||SUBSTR(HEX(MYFIELD),12,1)||        
      SUBSTR(HEX(MYFIELD),14,1),                                 
      ' ','0123456789',' ')<>' '                              
OR SUBSTR(HEX(MYFIELD),13,1) NOT IN ('F','D')                    

Have fun!

Richard



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Casey
Sent: Monday, July 13, 2009 2:04 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Finding invalid data in zoned numeric fields with SQL

Jim,

Try this. 

UPDATE MYTABLE
SET MYFIELD = 0
WHERE TRANSLATE(SUBSTR(HEX(MYFIELD),1,LENGTH(HEX(MYFIELD))-1),'
','0123456789',' ')<>' '

The TRANSLATE function converts all the numeric digits to blanks. If
anything non-blank is left, it would indicate an invalid numeric field.

You could also add a check to make sure the sign byte (nybble?!) was valid.

UPDATE MYTABLE
SET MYFIELD = 0
WHERE TRANSLATE(SUBSTR(HEX(MYFIELD),1,LENGTH(HEX(MYFIELD))-1),'
','0123456789',' ')<>' '
      OR SUBSTR(HEX(MYFIELD),LENGTH(HEX(MYFIELD)),1) NOT IN ('F','D')

Hope this helps!

Richard


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Essinger
Sent: Monday, July 13, 2009 12:56 PM
To: Midrange Systems Technical Discussion
Subject: Re: Finding invalid data in zoned numeric fields with SQL

Michael and Eric,

That will work, and I did consider that after submitting my question.  I am
still hoping for a more "Not Numeric" that would catch the error if there is
anything other than numbers in the field.

Thanks!

Jim

On Mon, Jul 13, 2009 at 10:43 AM, DeLong, Eric
<EDeLong@xxxxxxxxxxxxxxx>wrote:

> Wont this work?
>
> Update mytable set mypacked = 0
> Where hex(mypacked) = '40404040'
>
> -Eric
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Essinger
> Sent: Monday, July 13, 2009 11:08 AM
> To: Midrange Systems Technical Discussion
> Subject: Finding invalid data in zoned numeric fields with SQL
>
> 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?
>
> Thanks!
>
> Jim
> --