Good catch!

If the OrderQty field in the problem records is all blanks, would this work?

FROM datafile
WHERE HEX(OrderQty) <> '4040404040'

Adjust the '4040404040' based on the length of the OrderQty field.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, February 11, 2013 1:11 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL: how to filter out spaces in fields defined as packed

On 11 Feb 2013 09:57, Richard Casey wrote:
If the bad data in OrderQty is only blanks, this should work.

FROM datafile
WHERE SUBSTR(HEX(OrderQty),1,2) <> '40'

Bad idea for two reasons. If the Packed BCD represents an even-precision
versus an odd-precision, the first digit is always zero; i.e. testing for
'040' would be more appropriate. Besides that however, the 5P00 decimal
value 40755 which is the Packed BCD value represented in storage as
x'40755F', will *incorrectly* be omitted from the results; i.e.
SUBSTR(HEX(OrderQty), 1, 2) = '40' and that is an entirely
*correct* Packed Decimal representation.

Regards, Chuck
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,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at

This footnote confirms that this email message has been scanned by PineApp
Mail-SeCure for the presence of malicious code, vandals & computer viruses.


This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page