zOs/SQL/DIVCAST
-- cast between integer and character
-- convert chars to the corresponding integer value
--
-- OA1P.fqzCastSmall2C(15 ) --> x'000f'
-- OA1P.fqzCastSmall2C(-1 ) --> x'ffff'
-- OA1P.fqzCastI2C (258) --> x'00000102'
-- OA1P.fqzCastI2C (-2 ) --> x'fffffffe'
-- OA1P.fqzCastBig2C (527) --> x'000000000000020f'
-- OA1P.fqzCastBig2C (-4 ) --> x'fffffffffffffffc'
-- OA1P.fqzCastC2Small(x'0c') --> 12
-- OA1P.fqzCastC2Small(x'ff') --> 255
-- OA1P.fqzCastC2Small(x'0100') --> 256
-- OA1P.fqzCastC2Small(x'ffff') --> -1
-- OA1P.fqzCastC2Small(x'8000') --> -32768
-- OA1P.fqzCastC2I (x'8000') --> 32768
-- OA1P.fqzCastC2I (x'7fffffff') --> 2147483647
-- OA1P.fqzCastC2I (x'ffffffff') --> -1
-- OA1P.fqzCastC2Big (x'ffffffff') --> 4294967295
-- OA1P.fqzCastC2Big (x'0c') --> 12
-- OA1P.fqzCastC2Big (x'ffffffffffffffff') --> -1
--
--14. 1.16 W. Keller rename auf ...Cast... und korrekte Vorzeichen
-- 8. 1.10 W. Keller neu
--
set current sqlid = 'S100447';
drop function OA1P.c2d8;
drop function OA1P.c2d4;
drop function OA1P.c2d2;
drop function OA1P.c2d1;
drop function OA1P.fosC2I8;
drop function OA1P.fosC2I4;
drop function OA1P.fosC2I2;
drop function OA1P.fosC2I1;
drop function OA1P.fqzCastBig2C(bigint);
drop function OA1P.fqzCastBig2C(int, bigint);
drop function OA1P.fqzCastI2C(int);
drop function OA1P.fqzCastI2C(smallInt, int);
drop function OA1P.fqzCastSmall2C(smallInt);
drop function OA1P.fqzCastc2Big(varchar(8));
drop function OA1P.fqzCastc2i(varchar(4));
drop function OA1P.fqzCastc2small(varchar(2));
drop function OA1P.fqzCastc1i(char(1));
create function OA1P.fqzCastC1I(c char(1)) returns smallInt
deterministic no external action contains sql
return posStr(x'000102030405060708090A0B0C0D0E0F'
|| x'101112131415161718191A1B1C1D1E1F'
|| x'202122232425262728292A2B2C2D2E2F'
|| x'303132333435363738393A3B3C3D3E3F'
|| x'404142434445464748494A4B4C4D4E4F'
|| x'505152535455565758595A5B5C5D5E5F'
|| x'606162636465666768696A6B6C6D6E6F'
|| x'707172737475767778797A7B7C7D7E7F'
|| x'808182838485868788898A8B8C8D8E8F'
|| x'909192939495969798999A9B9C9D9E9F'
|| x'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF'
|| x'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'
|| x'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF'
|| x'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'
|| x'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF'
|| x'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF', c) - 1
;
create function OA1P.fqzCastC2Small(c varChar(2) for bit data)
returns smallInt
deterministic no external action contains sql
return case when length(c) = 2
then (mod(oa1p.fqzCastC1I(left(c, 1))
+ 128, 256) - 128) * 256
else 0 end
+ case when length(c) >= 1
then oa1p.fqzCastC1I(right(c, 1))
else 0 end
;
create function OA1P.fqzCastC2I(c varChar(4) for bit data)
returns int
deterministic no external action contains sql
return ( case when length(c) >= 3
then int(oa1p.fqzCastC2Small(left(c, length(c)-2)))*256
else 0 end
+ case when length(c) >= 2
then oa1p.fqzCastC1I(substr(c, length(c)-1, 1))
else 0 end
) * 256
+ case when length(c) >= 1
then oa1p.fqzCastC1I(right(c, 1))
else 0 end
;
create function OA1P.fqzCastC2Big(c varChar(8) for bit data)
returns bigint
deterministic no external action contains sql
return ((( case when length(c) > 4
then bigInt(oa1p.fqzCastC2I(left(c, length(c)-4)))*256
else 0 end
+ case when length(c) >= 4
then oa1p.fqzCastC1I(substr(c, length(c)-3, 1))
else 0 end
) * 256
+ case when length(c) >= 3
then oa1p.fqzCastC1I(substr(c, length(c)-2, 1))
else 0 end
) * 256
+ case when length(c) >= 2
then oa1p.fqzCastC1I(substr(c, length(c)-1, 1))
else 0 end
) * 256
+ case when length(c) >= 1
then oa1p.fqzCastC1I(right(c, 1))
else 0 end
;
create function OA1P.fqzCastSmall2C(i smallInt)
returns char(2) for bit data
deterministic no external action contains sql
return case when i >= 0
then ebcdic_chr(i / 256) || ebcdic_chr(mod(i, 256))
else ebcdic_chr((1 + i ) / 256 + 255)
||ebcdic_chr(mod(1+i , 256) + 255)
end
;
create function OA1P.fqzCastI2C(l smallInt, r int)
returns char(4) for bit data
specific "FQZCASTI2C(S,I)"
deterministic no external action contains sql
return oa1p.fqzCastSmall2c(l)
|| ebcdic_chr(r / 256)
|| ebcdic_chr(mod(r, 256))
;
create function OA1P.fqzCastI2C(i int) returns char(4) for bit data
specific "FQZCASTI2C(I)"
deterministic no external action contains sql
return case when i >= 0
then oa1p.fqzCastI2C(i / 65536, mod(i, 65536))
else oa1p.fqzCastI2C((1 + i ) / 65536 - 1
, mod(1+i , 65536) + 65535)
end
;
create function OA1P.fqzCastBig2C(l int, r bigInt)
returns char(8) for bit data
specific "FQZCASTBIG2C(S,I)"
deterministic no external action contains sql
return OA1P.fqzCastI2C(l)
|| ebcdic_chr(r / 16777216)
|| ebcdic_chr(mod(r, 16777216) / 65536)
|| ebcdic_chr(mod(r, 65536) / 256)
|| ebcdic_chr(mod(r, 256))
;
create function OA1P.fqzCastBig2C(i bigInt)
returns char(8) for bit data
specific "FQZCASTBIG2C(I)"
deterministic no external action contains sql
return case when i >= 0
then oa1p.fqzCastBig2C(i / 4294967296
, mod(i, 4294967296))
else oa1p.fqzCastBig2C((1 + i ) / 4294967296 - 1
, mod(1+i , 4294967296) + 4294967295)
end
;
-- compatibility: old names
create function OA1P.fosC2I2(c varchar(4) for bit data) returns int
deterministic no external action contains sql
return oa1p.fqzCastc2i(c)
;
create function OA1P.fosC2I4(c varchar(8) for bit data) returns bigInt
deterministic no external action contains sql
return oa1p.fqzCastc2Big(c)
;
with n (n) as (
select 0 from sysIbm.sysDummy1
union all select 1 from sysIbm.sysDummy1
union all select 5 from sysIbm.sysDummy1
union all select 17 from sysIbm.sysDummy1
union all select 254 from sysIbm.sysDummy1
union all select 255 from sysIbm.sysDummy1
)
, m (n) as
(
select int(n1.n) * 256 + n2.n
from n n1, n n2
)
, o (n) as
(
select bigint(n1.n) * 256 *256 + n2.n
from m n1, m n2
)
, p2 (n, e) as
(
select bigInt(1), 0 from sysibm.sysDummy1
union all select 2*n, e+1 from p2 where e < 62
)
, u(b) as
(
select n from o
union all select 9223372036854775807 from sysibm.sysDummy1
union all select 9223372036854775806 from sysibm.sysDummy1
union all select - n from o
union all select -9223372036854775808 from sysibm.sysDummy1
union all select -9223372036854775807 from sysibm.sysDummy1
union all select n from p2
union all select n-1 from p2
union all select n+1 from p2
union all select -n from p2
union all select -n-1 from p2
union all select -n+1 from p2
)
, v as
(
select int(max(min(b, 2147483647), -2147483648)) i, b
from u
)
, r2 as
(
select i, oa1p.fqzCastI2c(i) i2c
, oa1p.fqzCastc2i(oa1p.fqzCastI2c(i)) c2iI2c
, b, oa1p.fqzCastBig2c(b) big2c
, oa1p.fqzCastc2big(oa1p.fqzCastBig2c(b)) c2bigBig2c
from v
)
, r as
(
select r2.*
, case when i2c is null then 'i2c=null'
when hex(i2c) = hex(i) then 'i2c=i'
else 'i2c<>i' end
||case when c2ii2c is null then ' c2ii2c=null'
when c2ii2c = i then ' c2ii2c=i'
else ' c2ii2c<>i' end
||case when big2c is null then ' big2c=null'
when hex(big2c) = hex(b) then ' big2c=b'
else 'big2c<>b' end
||case when c2bigbig2c is null then ' c2bigbig2c=null'
when c2bigbig2c = b then ' c2bigbig2c=b'
else ' c2bigbig2c<>b' end res
from r2
)
select count(*), res, min(i), min(b)
from r
group by res
;