RE: Finding invalid data in zoned numeric fields with SQL

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

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