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

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

On Thu, 14 Feb 2013 at 15:12:26 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

> date: Thu, 14 Feb 2013 15:12:26 -0800
> from: CRPence <CRPbottle@xxxxxxxxx>
> subject: Re: how to filter out spaces in fields defined as packed
> 	numeric
>
> On 14 Feb 2013 14:15, Needles,Stephen J wrote:
> > I had to find and fix some data like this. I used the following to
> > find the rows that had hex'40' in a particular field.
> >
> > Select * from datafile where substr(trim(hex(orderqty)),1,2) = '40'
>
>   The TRIM scalar in the expression seems extraneous.?  Regardless...
>
>   As I noted in prior responses, for the first byte of an odd-precision 
> Packed Decimal column, the value '40' is completely valid.  The x'40' in 
> the first byte of storage for Packed BCD is legitimate, and therefore 
> should not be presumed to have originated as the EBCDIC blank x'40'. 

I still use RPG for this kind of clean-up.  A generic SQL approach would be nice but as Chuck has pointed out is not as easy at it might seem.

Using a simple RPG program that uses Read with a DS result field and then overlaying the DS with character versions of the fields to be tested works just fine as the result DS precludes the decimal data error that a conventional RPG op-code requires.  I have also used program described files, an API call or three and substrings to achieve the same effect in a generic program.

Don't recall if the OP was insistent on an SQL solution but there is certainly more than one way to skin the cat.


Jon Paris

www.partner400.com
www.SystemiDeveloper.com