MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » February 2013

RE: SQL: how to filter out spaces in fields defined as packed numeric



fixed

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.
****************************************************************************
********





-----






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

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact