Re: Converting Character Zoned Data to Numeric in SQL

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