Re: SQL: how to filter out spaces in fields defined as packed numeric

[CRPence <CRPbottle@xxxxxxxxx>]

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.