zOs/SQL/DIVFMT
--
-- format to the e8/e7 format
--
-- the e8 format is a short representation (8 byte)
-- of a possibly very large number with
-- precision better than 1% between 9e99 and 1e-9
-- examples ' 1.23e12', '-4.56e-3' ' 0.00e00'
-- the e7 format is even shorter (7 byte):
-- non negative numbers are the e8 format without leading space
-- negative numbers have only one digit less in the mantissa
-- examples '1.23e12', '-4.5e-3' '0.00e00'
-- binary suffix format -9999.99T mit oder ohne Nachkommastellen
--
-- decimal binary
-- k kilo 10e03 2**10 1.02e3 1024
-- M Mega 10e06 2**20 1.05e06 1048576
-- G Giga 10e09 2**30 1.07e09 1073741824
-- T Tera 10e12 2**40 1.10e12 1099511627776
-- P Peta 10e15 2**50 1.13e15 1125899906842624
-- E Exa 10e18 2**60 1.15e18 1152921504606846976
--
-- 4 predefined formats for bin and for dec
-- fqzFmtDec4 fqzFmtBin4 '123k' '-###'
-- fqzFmtDec5 fqzFmtBin5 ' 456k' '-456k'
-- fqzFmtDec7 fqzFmtBin7 '789.01 ' '-789.0 '
-- fqzFmtDec8 fqzFmtBin8 ' 789.01M' '-789.01M'
-- attention 1010 ==> 0.99k (binary)
--
-- time: format seconds to time: ---- 1s45, 12m13, 13h45 17d15 1234d
-- no error for underflow or overflow wich sometimes shows as ####
--
-- on a unicode table the result of the function is translated
-- and gets longer (3 times). to truncate back
-- either use substring around the function or preJoin sysDummyE
--
--
-- attention inline sql does not support versioning
-- you cannot drop a function if used in view or other function
-- to drop, change functions, recreate use
-- planTb: fun=vfD ... vfC
-- ddlChddl: drop ... recreate
-- prFunWai: drop ... recreate
--
-- 8.2.16 W. Keller rename to qz, incl fmTime, add um Bin und dec
-- 7.1.10 W. Keller neu
--
set current sqlid = 'S100447';
$@ if 1 then $@=/drop/
drop function oa1p.fosFmTime(s real) ;
drop function oa1p.fosFmtE7(r real);
drop function oa1p.fosFmtE8(r real);
drop function oa1p.fqzFmTime(s real) ;
drop function oa1p.fqzFmtInt2(v int, m int, c varchar(1));
drop function oa1p.fqzFmtE7(r real);
drop function oa1p.fqzFmtE8(r real);
drop function oa1p.fqzFmtEU(r real, decP Int);
drop function OA1P.fqzFmtE(ma real, ex int) ;
drop function OA1P.fqzFmtE(ma real, ex int, decP int) ;
drop function oa1p.fqzFmtDec8(i real);
drop function oa1p.fqzFmtDec7(i real);
drop function oa1p.fqzFmtDec5(i real);
drop function oa1p.fqzFmtDec4(i real);
drop function oa1p.fqzFmtBin8(i real);
drop function oa1p.fqzFmtBin7(i real);
drop function oa1p.fqzFmtBin5(i real);
drop function oa1p.fqzFmtBin4(i real);
drop function oa1p.fqzFmtSuf(i real, s int, decR int, b real);
drop function oa1p.fqzFmtLR(i real, decL int, decR int) ;
commit;
$/drop/
-- return a string -999.999 with - or ' ' as sign
-- decL leading digits (leading 0 to ' ', but ' 0.3'
-- a decimal point if decR > 0
-- and decR trailing digits, rounded ----------------------
create function oa1p.fqzFmtLR(i real, decL int, decR int)
returns varchar(20)
deterministic no external action contains sql
return varchar_format(i, substr('9999999990.999999999'
, 11 - decL , decL + decR + min(1, decR)))
;
--#SET TERMINATOR #
-- format with suffix for k,M etc, 3 digits before dec Point
-- i input
-- s 1 = extra character for sign, 0 = truncate dec places for '-'
-- decR decimal places right of decPoint. omit decPoint if 0
-- b base 1000 ==> decimal, 1024 == binary
create function oa1p.fqzFmtSuf(i real, s int, decR int, b real)
returns varchar(15)
deterministic no external action contains sql
begin
declare dR, e int;
declare j real;
declare res varchar(15);
set dR = decR;
if s = 0 and i < 0 then
if decR <= 0 then
return '-###';
end if;
set dR = dR-1;
end if;
set e = floor(ln(max(1, abs(i)))/ln(b));
if e > 6 then
set e = 6;
-- elseIf round(j, dR) >= 1000 then ==> this better code
-- set j = j / b; ==> need reads sql err.ec65
-- set e = e + 1; ==> instead we use like
-- end if;
end if;
set j = i / power(b, e);
set res = oa1p.fqzFmtLR(j, 3, dR);
if res like '#%' and e < 6 then
set e = e + 1;
set res = oa1p.fqzFmtLR(j / b, 3, dR);
end if;
return case when s <> 0 then res
when i >= 0 then substr(res, 2)
else right(' ' || res, 4+decR)
end || substr(' kMGTPE', e+1, 1);
end
#
--#SET TERMINATOR ;
--- 990G: binary suffix no dec digits -------------------------------
create function oa1p.fqzFmtBin4(i real)
returns char(4)
deterministic no external action contains sql
return oa1p.fqzFmtSuf(i, 0, 0, 1024);
;
--- -990G: binary suffix no dec digits -------------------------------
create function oa1p.fqzFmtBin5(i real)
returns char(5)
deterministic no external action contains sql
return oa1p.fqzFmtSuf(i, 1, 0, 1024);
;
--- 990.99G: binary suffix 2 dec digits ---------------------------
create function oa1p.fqzFmtBin7(i real)
returns char(7)
deterministic no external action contains sql
return oa1p.fqzFmtSuf(i, 0, 2, 1024);
;
--- -990.99G: binary suffix 2 dec digits -----------------------------
create function oa1p.fqzFmtBin8(i real)
returns char(8)
deterministic no external action contains sql
return oa1p.fqzFmtSuf(i, 1, 2, 1024);
;
--- 990G: decimal suffix no dec digits -------------------------------
create function oa1p.fqzFmtDec4(i real)
returns char(4)
deterministic no external action contains sql
return oa1p.fqzFmtSuf(i, 0, 0, 1000);
;
--- -990G: decimal suffix no dec digits -------------------------------
create function oa1p.fqzFmtDec5(i real)
returns char(5)
deterministic no external action contains sql
return oa1p.fqzFmtSuf(i, 1, 0, 1000);
;
--- 990.99G: decimal suffix 2 dec digits ---------------------------
create function oa1p.fqzFmtDec7(i real)
returns char(7)
deterministic no external action contains sql
return oa1p.fqzFmtSuf(i, 0, 2, 1000);
;
--- -990.99G: decimal suffix 2 dec digits -----------------------------
create function oa1p.fqzFmtDec8(i real)
returns char(8)
deterministic no external action contains sql
return oa1p.fqzFmtSuf(i, 1, 2, 1000);
;
-- move exponent to [-9, 99] and mantissa to 1 digit before point -----
create function oa1p.fqzFmtE(ma real, ex int, decP int)
returns varchar(20)
specific "FQZFMTE(R,I,I)"
deterministic no external action contains sql
return case when ex < -9
then oa1p.fqzFmtLR(ma / power(10, -9-ex),1,decP)||'e-9'
when abs(round(ma, decP)) < 10 -- exponent is alwys < 99
then oa1p.fqzFmtLR(ma, 1, decP)
|| 'e' || right('0' || ex, 2)
else oa1p.fqzFmtLR(ma / 10, 1, decP)
|| 'e' || right('0' || (ex+1), 2)
end
;
--- split mantissa and exponent ---------------------------------------
create function oa1p.fqzFmtE(r real, decP int)
returns varchar(20)
specific "FQZFMTE(R,I)"
deterministic no external action contains sql
return oa1p.fqzFmtE(
real(left(char(r), posstr(char(r), 'E') - 1)),
int(substr(char(r), posstr(char(r), 'E') + 1))
, decP)
--- handle sign '' for + decrease precision for - --------------------
create function oa1p.fqzFmtEU(r real, decP int)
returns varchar(20)
deterministic no external action contains sql
return case when r >= 0 then substr(oa1p.fqzFmtE(r, decp), 2)
when decP < 1 then '-e##'
else oa1p.fqzFmtE(r, decp-1)
end
;
--- e8 format +=' ', 2 decimals, totalLen=8 ---------------------------
create function oa1p.fqzFmtE8(r real)
returns char(8)
deterministic no external action contains sql
return oa1p.fqzFmtE(r, 2)
;
--- e7 format +='', 2 decimals, totalLen=7 ----------------------------
create function oa1p.fqzFmtE7(r real)
returns char(7)
deterministic no external action contains sql
return oa1p.fqzFmtEU(r, 2)
;
create function oa1p.fqzFmtInt2(v int, m int, c varchar(1))
returns char(5)
deterministic no external action contains sql
return right(' ' || strip(char(v/m)), 2) || c
|| right(digits(mod(v, m)), 2)
;
create function oa1p.fqzFmTime(s real) returns char(5)
deterministic no external action contains sql
-- convert integer seconds s to a time string
return case
when s < 0 then '-----'
when round(s*100, 0) < 6000 then
oa1p.fqzFmtInt2(int(round(s*100, 0)), 100, 's')
when round(s, 0) < 3600 then
oa1p.fqzFmtInt2(int(round(s, 0)), 60, 'm')
when round(s / 60, 0) < 1440 then
oa1p.fqzFmtInt2(int(round(s / 60, 0)), 60, 'h')
when round(s/3600, 0) < 2400 then
oa1p.fqzFmtInt2(int(round(s/3600, 0)), 24, 'd')
when round(s/86400, 0) < 10000 then
right(' ' || int(round(s/86400, 0)), 4)
|| 'd'
when s is null then null
else '+++++'
end
;
-- compatibility: old names
--- e8 format +=' ', 2 decimals, totalLen=8 ---------------------------
create function oa1p.fosFmtE8(r real)
returns char(8)
deterministic no external action contains sql
return oa1p.fqzFmtE(r, 2)
;
--- e7 format +='', 2 decimals, totalLen=7 ----------------------------
create function oa1p.fosFmtE7(r real)
returns char(7)
deterministic no external action contains sql
return oa1p.fqzFmtEU(r, 2)
;
create function oa1p.fosFmTime(s real) returns char(5)
deterministic no external action contains sql
return oa1p.fqzFmTime(s)
;
commit
;
$@ if 1 then $@=/test/
with reals (r ) as
(
select real(0)+0 from sysibm.sysDummy1
union select real(123.456) from sysibm.sysDummy1
union select real(999.456) from sysibm.sysDummy1
union select real(1005.456) from sysibm.sysDummy1
union select real(1015.456) from sysibm.sysDummy1
union select real(1024.456) from sysibm.sysDummy1
union select real(-23.446) from sysibm.sysDummy1
union select real(-23.456e7) from sysibm.sysDummy1
union select real(-23.436e67) from sysibm.sysDummy1
union select real(+23.456e57) from sysibm.sysDummy1
union select real(-23.476e17) from sysibm.sysDummy1
union select real(-23.556e-6) from sysibm.sysDummy1
union select real( 1090000000000) from sysibm.sysDummy1
union select real(-1090000000000) from sysibm.sysDummy1
union select real(-23.456e-16) from sysibm.sysDummy1
union select real(+99.999e9 ) from sysibm.sysDummy1
union select real(-99.999e-2 ) from sysibm.sysDummy1
union select real(-10000)/7 from sysibm.sysDummy1
union select real(3e-10) from sysibm.sysDummy1
)
select r
-- , '<' || oa1p.fqzFmtE8(r) || '>'
-- , '<' || oa1p.fqzFmtE7(r) || '>'
-- , case when right(oa1p.fqzFmtE8(r), 1) = '-' then '--'
-- when abs(abs(real(uCase(oa1p.fqzFmtE8(r))))-abs(r))
-- > .004 * abs(r)
-- then 'e8' else '' end
-- , case when right(oa1p.fqzFmtE7(r), 1) = '-' then '--'
-- when abs(abs(real(uCase(oa1p.fqzFmtE7(r))))-abs(r))
-- > .04 * abs(r)
-- then 'e7' else '' end
, '<' || oa1p.fqzFmtBin4(r) || '>' FmtBin4
, '<' || oa1p.fqzFmtBin5(r) || '>' FmtBin5
, '<' || oa1p.fqzFmtBin7(r) || '>' FmtBin7
, '<' || oa1p.fqzFmtBin8(r) || '>' FmtBin8
, '<' || oa1p.fqzFmtDec4(r) || '>' FmtDec4
, '<' || oa1p.fqzFmtDec5(r) || '>' FmtDec5
, '<' || oa1p.fqzFmtDec7(r) || '>' FmtDec7
, '<' || oa1p.fqzFmtDec8(r) || '>' FmtDec8
, '<' || oa1p.fqzFmtE7(r) || '>'
, '<' || oa1p.fqzFmtE8(r) || '>'
-- , round(r,2)
-- , '<' || char(real(r)) || '>', length(char(r))
from reals
;
with r(r) as
(
select real(123.45) from sysibm.sysDummy1
union select -123.45 from sysibm.sysDummy1
union select 67.894 from sysibm.sysDummy1
union select 4.566 from sysibm.sysDummy1
union select 2 from sysibm.sysDummy1
union select 0.127 from sysibm.sysDummy1
union select 0.07 from sysibm.sysDummy1
union select 0 from sysibm.sysDummy1
union select -0.341 from sysibm.sysDummy1
union select -0.087 from sysibm.sysDummy1
union select 5432.19 from sysibm.sysDummy1
union select-5432.19 from sysibm.sysDummy1
)
select '<' || r || '>' chr
, '<' || decfloat(r) || '>' decFlo
, '<' || dec(r, 6, 2) || '>' dec
, '<' || varchar_Format(r, '990.99') || '>' varchar
from r
;
select power(3,3), power(3.0,-3) from sysibm.sysDummy1;
;
select '<' || oa1p.fqzFmtLR(2.34e2, 1, 2)||'>'from sysibm.sysdummy1;
select '<' || oa1p.fqzFmtLR(real(2.34e2),1,2)||'>'from sysibm.sysdummy1;
select oa1p.fqzFmtE8(real(2.34)) from sysibm.sysdummy1;
select oa1p.fqzFmtE7(real(2.34)) from sysibm.sysdummy1;
with v(v) as
(
select 0 from sysibm.sysDummy1
union all select -1 from sysibm.sysDummy1
union all select -1 from sysibm.sysDummy1
union all select -0.0000375 from sysibm.sysDummy1
union all select +432109.5 from sysibm.sysDummy1
)
, r(r) as
(
select real(v) from v
)
select r, '<' || char(r) || '>' chr
, '<' || dec(r, 12,5) || '>' deci
, '<' || varchar_Format(r, 'S990.99999') || '>' deci
, insert('abc', 2, 0, 'fifi')
from r
;
$/test/