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
reads sql data
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;
return ToUnits;
/*return ToUnits;*/
end for;
return null;
end!
select imsrtx,
'1' || imuom1 || ' =', JDEConvert(imitm, imuom1, 'LB', 1) || ' lbs.',
'1' || imuom2 || ' =', JDEConvert(imitm, imuom2, 'LB', 1) || ' lbs.'
from f4101
(In the past, we had a function that found the requested conversion by recursively searching direct conversions till it found a path between the From UOM and the ToUOM. We all know recursion is more fun and usually requires less code. In this case the recursive method was less efficient and created more problems than it solved, so we sadly had to rewrite to this method, which we are just as proud of.)
Pretty clever and efficient way to convert between units of measure. I wrote one myself but It was a lot heavier and convoluted :-(.
The thing is my monstrosity managed a way to take into account conversions found in the F41003 table. If you rely on those this script will just skip them.
Do you mind sharing your thoughts on incorporating the F41003 table into the mix?
I never included those because we weren’t using them, but it is a fair point. Double-check my logic here, but I would add to the cfF41002 query a union to F41003, joined to itself the same way as F41002, with similar criteria, and add a sort column so the F41002 conversions came first and then everything should work great.