Re: Finding invalid data in zoned numeric fields with SQL

[Jim Essinger <dilbernator@xxxxxxxxx>]

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
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.