As long as we are discussing JD Edwards running on DB/2. Here’s another potentially useful function.  This one converts a value from one unit of measure to another based on the F41002 table.  It does this by converting the FromUOM to the primary UOM and thence to the ToUOM.

```--#SET DELIMITER !
drop function myuser.JDEConvert!

CREATE function myuser.JDEConvert(Item decimal(10,0), FromUOM varchar(3),
ToUOM varchar(3), Units decimal(15,7))
returns decimal(15,7)
language sql
not deterministic
begin atomic

declare PrimaryUnits decimal(15,7);
declare ToUnits decimal(15,7);

/*declare exit handler for sqlexception
return null;*/

/* This is for the sake of speed mainly */
if Units = 0 then
return 0;
end if;
if FromUOM = ToUOM then
return Units;
end if;

/* -----------------------------------------------------------------
we know we don't have any conversion directly between the from unit
and the to unit, so what we do is look for a conversion like so...
FromUOM to PrimaryUOM to ToUOM
----------------------------------------------------------------- */
for cfF41002 as
select f.umum fum, f.umrum frum, round(f.umconv/10000000,7) fconv, round(f.umcnv1/10000000,7) fcnv1,
t.umum tum, t.umrum trum, round(t.umconv/10000000,7) tconv, round(t.umcnv1/10000000,7) tcnv1
from f41002 f join F41002 t on t.umitm = f.umitm
where f.umitm = Item
and FromUOM in (f.umum, f.umrum)
and ToUOM in (t.umum, t.umrum)
do
/* This could be slightly simplified, but this way is easier to read I think. */
if fum = FromUOM then
set PrimaryUnits = Units * fcnv1;
else
set PrimaryUnits = (Units / fconv) * fcnv1;
end if;

if tum = ToUOM then
set ToUnits = PrimaryUnits / tcnv1;
else
set ToUnits = (PrimaryUnits * tconv) / tcnv1;
end if;