× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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:
....+...
ORDERQTY
40,345
******** 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'


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.