Re: Converting Character Zoned Data to Numeric in SQL

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Chuck

Had forgot about that - it does the trick and beautifully! Here's the 
result -

SELECT
 case
   when substr(chr1,length(chr1),1) between '0' and '9' then 1
   else -1
 end *
 zoned(
       substr(chr1,1,length(chr1)-1) concat
       lor(x'F0', substr(chr1,length(chr1),1))
 )
FROM vern/chrtozon

The CASE construct determines the sign.

The LOR forces the final character to a positive number.

So it's the sign times the absolute value - which would not be usable 
here, I suppose.

VOILA!

BTW, I'll be in Rochester mid-November - maybe can get together.

Vern

CRPence wrote:
>    The LAND [Logical AND] scalar?  I referenced that [there are 
> other logical operators] on a similar subject:
> http://archive.midrange.com/midrange-l/200907/msg00517.html
>
> Regards, Chuck
>
> Vern Hamberg wrote:
>   
> > Yeah, just did an RTFM - wishful thinking this morning before
> > going to work. Sigh!!
> >
> > Later Vern
> >
> > rob@xxxxxxxxx wrote:
> >     
> > > There is no BITAND yet available in SQL for DB2 for i.  Submit
> > > a DCR and see what happens. 
> > > http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscale.htm
> > >
> > > https://www-912.ibm.com/r_dir/ReqDesChange.nsf/Request_for_Design_Change?OpenForm
> > >
> > >