Hi Gerald,
I took one of the samples at the link you posted and tweaked it a bit for
DB2 and to handle negative numbers. This doesn't handle decimal places
greater than 3.
create or replace function
YOUR_SCHEMA.DECIMAL_FORMAT_FRACTION
( IN_DECIMAL decimal( 15,3 )
)
returns varchar( 32 )
language sql
specific YOUR_SCHEMA/DECFMTFRAC
not deterministic
allow parallel
no external action
modifies SQL data
returns null on null input
fenced
set option dbgview = *source
,commit = *nc
,closqlcsr = *endmod
,dftrdbcol = *none
begin
declare strResult varchar(32) default null;
with s as ( select IN_DECIMAL as num from ( values( 1 ) ) as V )
,t as ( select level l from s connect by level <= 1000 )
,d as ( select s.num, min(t.l) as denominator
from s, t
where s.num * t.l = floor(s.num * t.l)
group by s.num
)
,r as ( select d.num
,i.integer_val
,n.numerator
,d.denominator
,varchar( i.integer_val ) concat
case d.denominator * rv.remainder_val
when 0 then ''
else ' ' concat varchar( n.numerator ) concat '/'
concat varchar( d.denominator )
end representation
from d
cross join lateral ( values case when d.num >= 0 then
floor(d.num) else ceiling(d.num) end ) as i ( integer_val )
cross join lateral ( values d.num - i.integer_val ) as rv (
remainder_val )
cross join lateral ( values int( abs( d.denominator *
rv.remainder_val ) ) ) as n ( numerator )
)
select representation
into strResult
from r
;
return strResult
;
end
Mike
date: Wed, 21 Dec 2016 11:00:34 -0600
from: Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>
subject: SQL user function to turn a decimal number to a fraction
I am looking for a sql function that would turn a decimal number to a
fraction.
I found the following, for ORACLE... and tried to make it work for DB2 for
i...
but I am not nearly capable for that...
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_
QUESTION_ID:410098200346123548
midrange.com
