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

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.






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