×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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.
midrange.com
