On 11 Feb 2013 11:29, Richard Casey wrote:
CRPence on Monday, February 11, 2013 1:11 PM wrote:
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'
Inline below I make a couple revisions to my first reply, plus a
response to the more recent quoted message.
The above predicate is functional, with the given assumption, but
only when the column OrderQty is either even-precision or
single-precision. The following table should clarify; although noting
the zero scale shown for each Packed _type_ is not germane, as the
number of decimal places is irrelevant:
_type_ _value_ _ability of above predicate to find bad data_
1P00 x'40' works per sign-nibble=0, but the 4 is valid
2P00 x'4040' works per first-nibble=4, but must be zero
3P00 x'4040' fails per first '40' is valid for a DEC(3)
4P00 x'404040' works per first-nibble=4, but must be zero
5P00 x'404040' fails per first '40' is valid for a DEC(3)
// the repeating even-works\odd-fails pattern is conspicuous
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.
The above statement as the /first reason/ is wrong. I retract that.
Had I said "testing for '0040' would be more appropriate", then I
would have been closer [being in sync with my error], but I still would
have been wrong. The next paragraph just explains the origin of my
error; easily skipped.
The HEX scalar [as implemented, and presumably that will not change]
does not force the first nibble to a zero for an even-precision. The
first nibble seems to be ignored [effectively is treated as zero] for
editing\presentation in most cases [hex digits 0-9 only?], possibly also
ignored for calculations [untested], and possibly also ignored for some
comparisons as well [although not for the DB with file data, but via
Access Path was untested]. So my mistake originated with thinking about
an edited\presented decimal with a non-zero first nibble, and applying
that thought to the HEX scalar; i.e. the 2P00 value x'4040' will be
presented as decimal 04 using editing [MI EDIT instruction], but the HEX
scalar applied to that same value stored in a 2P00 will be the string
'4040' rather than the string '0040'.
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.
Good catch!
Yes, but as noted above...
The first reason listed was "wrong" as written. With the same
assumption of all\only blanks, then either of the following two
predicates would function correctly, but again, only for even-precision
columns [with the first being a variation of the original predicate
offered]:
left(HEX(OrderQty),2)<>'40' /* non-zero first nibble is four */
// and next is zero, so almost surely a blank was written
left(HEX(OrderQty),1)<>'4' /* although, any non-zero is bad */
// so, no matter what follows; likely a blank was written
The second reason listed was at least somewhat deceptive as written,
because I failed to mention explicitly its applicability only to
odd-precision; merely that even-precision was noted in the first, and
specifically using odd-precision in the second, may not have made that
obvious. Anyhow, to reiterate with that clarification... Using the
predicate left(HEX(OrderQty),2)<>'40' _for an odd-precision column_ can
not distinguish between a valid number starting with the digits "40" and
a value having been corrupted with a blank [in its first byte]; i.e. the
HEX representations of both the corrupted numeric and the valid numeric
will appear as a valid number starting with the digits "40". That is
represented in the table of type\value\predicate-ability included
earlier; i.e. how the predicate fails for all odd-precisions except
single-precision.
If the OrderQty field in the problem records is all blanks, would
this work?
SELECT *
FROM datafile
WHERE HEX(OrderQty) <> '4040404040'
Yes. That works for both even-precision [of eight] and odd-precision
[of nine].
With the same assumption, that the specific corruption is all\only
blanks in the buffer of the Packed numeric field, then the predicate I
gave in my first reply in this message thread compares less data, is
also functional irrespective of even-precision versus odd-precision, but
also remains functional with every supported precision 1-63:
http://archive.midrange.com/midrange-l/201302/msg00557.html
right(Hex(OrderQty), 1) <> '0' /* or = '0' to find bad rows */
The reason I started with the RIGHT scalar [vs LEFT or SUBSTR(x,1,2)]
is because the zero in the sign nibble would effect the decimal data
error when all x'40's are written as Packed BCD. The sign nibble is the
second nibble of the last\rightmost byte [the sign byte], which is the
last\rightmost byte and thus the last hex digit in the hex
representation of the value. Therefore a predicate more conspicuously
specific to comparing for blanks, although slightly less efficient than
the prior, is just as good as the one above:
right(Hex(OrderQty), 2)<>'40' /* or = '40' to find bad rows */
Adjust the '4040404040' based on the length of the OrderQty field.
Yes. However... To make that predicate functional irrespective of
the column precisions [again, with same assumption of all\only blanks as
corruption], to avoid /adjusting/ the literal\constant referenced in the
predicate based on the column precision, I would probably use something
like the following expression [instead of the literal value] against
which to compare:
SELECT *
FROM Datafile
WHERE HEX(OrderQty) <> repeat('40', length(OrderQty))
midrange.com
