zOs/SQL/FOSXX

set current sqlid = 'S100447';
-- drop   function A540769.tosXX(a float, b float, c float);
terminator?;
create function A540769.tosXX(a float, b float, c float)
          returns  varchar(20)
          deterministic
begin
    declare sm float;
    declare res, a1, a2 varchar(30) default '';
    declare la, cu char(4);
    declare cx int;
    set sm = a+b+c;
    if sm < 0 then
        return oa1a.fosFmtE8(sm) || ' |||';
    elseif sm < 1e-9 then
        return oa1a.fosFmtE7(sm);
    end if;
    set a1  =
           case when a      <= sm/200 then ''
          else digits(dec(round(min(99, 100 * a / sm)), 2))||'a ' end
        || case when b      <= sm/200 then ''
          else digits(dec(round(min(99, 100 * b / sm)), 2))||'b ' end
        || case when c      <= sm/200 then ''
          else digits(dec(round(min(99, 100 * c / sm)), 2))||'c ' end;
    set la = x'ffffffff';
    loop
        set cx = 1;
        set cu = '';
        while cx < length(a1) do
            if substr(a1, cx, 4) < la and substr(a1, cx, 4) > cu then
                set cu = substr(a1, cx, 4);
            end if;
            set cx = cx + 4;
        end while;
        set a2 = a2 || cu;
        if cu = '' or length(a2) >= 12 then
            return oa1a.fosFmtE7(sm) || ' ' || a2;
        end if;
        set la = cu;
    end loop;
end
?
terminator;?

select A540769.tosxx( 3 , -1   , -3)
    from sysibm.sysDummy1
;
rollback
;x;
--     convert chars to the corresponding integer value
--      e.g. x'FF' -> 255, ' ' -> 64, '010000' -> 65536
--  UDFs    oa1a.fosC2I1: char(1) --> int
--          oa1a.fosC2I2: varchar(2) --> int
--          oa1a.fosC2I4: varchar(4) --> bigInt
--          oa1a.fosC2I8: varchar(8) --> bigInt
--                    values >= x'80000000000000' yield fixPointOverflow
--                           or negativ results due to bigInt arithmetic
-- 8.1.10 W. Keller neu
--
set current sqlid = 'S100447';
--  drop function OA1a.c2d8;
--  drop function OA1a.c2d4;
--  drop function OA1a.c2d2;
--  drop function OA1a.c2d1;
    drop function oa1a.fosC2I8;
    drop function oa1a.fosC2I4;
    drop function oa1a.fosC2I2;
    drop function oa1a.fosC2I1;
create function oa1a.fosC2I1(c char(1)) returns int
    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 oa1a.fosC2I2(c varchar(2)) returns int
    return case when length(c) = 0 then -1
                when length(c) = 1 then oa1a.fosC2I1(char(c, 1))
                else        oa1a.fosC2I1(char(left(c, 1), 1)) * 256
                          + oa1a.fosC2I1(char(substr(c, 2), 1))
           end
;
create function oa1a.fosC2I4(c varchar(4)) returns bigInt
    return case when length(c) < 3 then oa1a.fosC2I2(c)
                else   oa1a.fosC2I2(left(c, length(c) - 2))
                       * bigInt(65536)
                       + oa1a.fosC2I2(right(c, 2))
           end
;
create function oa1a.fosC2I8(c8 varchar(8)) returns bigInt
    return case when length(c8) < 5 then oa1a.fosC2I4(c8)
                else   oa1a.fosC2I4(left(c8, length(c8) - 4))
                       * bigInt(4294967296)
                       + oa1a.fosC2I4(right(c8, 4))
           end
;
with i (i) as
(
select ''    from sysibm.sysDummy1
union select x'03' from sysibm.sysDummy1
union select x'0C' from sysibm.sysDummy1
union select x'1E' from sysibm.sysDummy1
union select x'40' from sysibm.sysDummy1
union select x'AA' from sysibm.sysDummy1
union select x'FF' from sysibm.sysDummy1
union select x'6407' from sysibm.sysDummy1
union select x'0009' from sysibm.sysDummy1
union select x'9999' from sysibm.sysDummy1
union select x'FFFF' from sysibm.sysDummy1
union select x'01FF' from sysibm.sysDummy1
union select x'01FFFF' from sysibm.sysDummy1
union select x'01FFFFFF' from sysibm.sysDummy1
union select x'FFFFFFFF' from sysibm.sysDummy1
union select x'FFFFFFFFFF' from sysibm.sysDummy1
union select x'FFFFFFFFFFFF' from sysibm.sysDummy1
union select x'FFFFFFFFFFFFFF' from sysibm.sysDummy1
union select x'7FFFFFFFFFFFFFFF' from sysibm.sysDummy1
union select 'abcdefgh'          from sysibm.sysDummy1
union select x'8000000000000000' from sysibm.sysDummy1
union select x'8000000000000001' from sysibm.sysDummy1
union select x'BFFFFFFFFFFFFFFF' from sysibm.sysDummy1
union select x'C000000000000000' from sysibm.sysDummy1
union select x'C000000000000001' from sysibm.sysDummy1
union select x'FFFFFFFFFFFFFFFE' from sysibm.sysDummy1
union select x'FFFFFFFFFFFFFFFF' from sysibm.sysDummy1
)
select hex(i), length(i), oa1a.fosC2I8(i)
    from i
;
commit