On 14 Feb 2013 14:15, Needles,Stephen J wrote:
I had to find and fix some data like this. I used the following to
find the rows that had hex'40' in a particular field.
Select * from datafile where substr(trim(hex(orderqty)),1,2) = '40'
The TRIM scalar in the expression seems extraneous.? Regardless...
As I noted in prior responses, for the first byte of an odd-precision
Packed Decimal column, the value '40' is completely valid. The x'40' in
the first byte of storage for Packed BCD is legitimate, and therefore
should not be presumed to have originated as the EBCDIC blank x'40'.
Here is a script showing a valid decimal value being selected as one
which has the x'40', but is that very helpful when invalid decimal data
is being sought?:
create table qtemp/dec5 (orderqty, dec(5))
insert into qtemp/dec5 values(40345)
select * from qtemp/dec5 where substr(trim(hex(orderqty)),1,2) = '40'
; -- report follows:
******** End of data ********
If the test is going to be limited to one byte of data, then use the
sign byte, which is the last byte. The following predicate would be
more appropriate to find out if the OrderQty field had EBCDIC blanks
written to its storage location instead of valid Packed BCD data:
right(hex(orderqty), 2) = '40'