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 08:34, Stone, Joel wrote:
I have a file with hex '40' spaces in a field OrderQty defined to
DDS as pack numeric.

x'40' is valid Packed BCD data, except the byte with the sign nibble, the last byte.

I am using SQL to read the file and SQL croaks when it reads these
nasty invalid records.

As it should, to protect from decisions based on garbage-in; i.e. to avoid garbage-out. Use an SQL TABLE instead of a DDS created PF, and there is built-in protection from garbage-in.

Is it possible to filter out these records in SQL?

Yes. Easiest with a UDF for full validation, but /only/ with the HEX function [HEX(column_name) as input to the UDF], because any reference to the numeric column without the HEX scalar *should* fail.

For example something like:

SELECT * from Datafile where left(OrderQty,1) = *blanks

That expression refers to the numeric column rather than the HEX() of the column. That implicit cast to character would fail because that type of casting must have valid decimal data from which to convert into digits, sign, decimal separator, and [supports only right] padding.

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

Here is the SQL error:
Data mapping error on member SQLTEMP1.
A data mapping error occurred on field OrderQty in
1 -- There is data in a decimal field that is not valid.

FWiW there are error message identifiers for a reason; they are not translatable and thus are capable of being searched irrespective of native language.






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