MIDRANGE dot COM Mailing List Archive


fixed

On 11 Feb 2013 09:25, CRPence wrote:
<<SNIP>>

Better to do this:

select RRN(A) , A.*
from Datafile as A
where right(hex(OrderQty, 1) = '0' /* note: the char not hex zero */
/* or a more thorough test: not in ('D','F') */
/* or to be entirely accurate: not in ('A','B','C','D','E','F') */
/* I think I have published a UDF here to validate every byte */

<<SNIP>>

I could not find a UDF that I had offered previously. Probably because instead, I had just given the expressions. The expressions alone are not so complex as to require a separate UDF, at least for when the issues are [typically] for ad hoc inquiries about a specific column. The following for example, is a thorough validation of the Packed BCD column OrderQty irrespective of precision and scale and sign:

≥ select rrn(a) , a.*
≥ from Datafile as a
≥ where right(hex(a.OrderQty), 1) not in ('A','B','C','D','E','F')
≥ or translate( left(hex(a.OrderQty), length(hex(a.OrderQty))-1)
≥ , ' ' /* decimal digits to blanks; blank pad */
≥ , '0987654321' ) <> ''

I forgot to clarify before... Use the inverse logic for selecting only the rows with valid decimal data for the OrderQty column, and drop the RRN which is there only to identify which rows are incorrect and thus need correction.

kwds: sql validate packed decimal data udf create function




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

This mailing list archive is Copyright 1997-2015 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