Good catch!
If the OrderQty field in the problem records is all blanks, would this work?
SELECT *
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
numeric
On 11 Feb 2013 09:57, Richard Casey wrote:
If the bad data in OrderQty is only blanks, this should work.
SELECT *
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,
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 footnote confirms that this email message has been scanned by PineApp
Mail-SeCure for the presence of malicious code, vandals & computer viruses.
****************************************************************************
********
-----
midrange.com
