Re: Finding invalid data in zoned numeric fields with SQL

[Jim Essinger <dilbernator@xxxxxxxxx>]

Thanks all.  Several suggestions worked well.  I chose the 2nd statement
that Elvis came up with as it seemed the easiest to understand for
maintenance.  I was hoping that a Zoned(myField), DEC(myField) or a
Char(myField) would return a null that could be tested (Where Zoned(myField)
is null).  What I get is an data decimal error when SQL tries to retrieve
the records.

Jim


On Mon, Jul 13, 2009 at 1:55 PM, Elvis Budimlic <
ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> 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
>
> Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
> OS/400
> www.centerfieldtechnology.com
>
>
> -----Original Message-----
> Subject: Re: Finding invalid data in zoned numeric fields with SQL
>
> Elvis,
>
> Nice thought!  The concern I have is if the first position of the field has
> an "F" value in the left nibble for that byte, that does not mean the rest
> of the field has valid values. I think I am going to have to test all bytes
> for valid values, if SQL does not have  nice function to do it for me. That
> could get a little ugly with an 8 digit field.
>
> Yes - I believe the software vendor did create this with DDS, and even
> thought it is defined as Zoned, it somehow allows invalid data to be added
> to the file in 4 fields.
>
> 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.