SQL 2K and UDF with recursivity

• dirkpitt01

  SSC Rookie

  Points: 35

  More actions

  May 20, 2009 at 4:03 pm

  #100633

  Hi guys,

  You may be bored with this kind of questions. I need to create a uder defined function that calculates conversions between Unit Of Measure (UOM) and my data structure is like :

  generic UOM conversion

  From To Conversion

  KG GM 1000

  TN KG 1000

  specific UOM conversion per article

  Article From To Conversion

  Article1 Pallet Layer 10

  Article1 Layer box 20

  Article1 box KG 5

  Article2 Pallet Layer 20

  Article2 Layer box 10

  Article2 box KG 10

  I am stuck here. We use SQL2000.

  I'd like to build a UDF that will use the parameter Article, FromUOM, ToUOM

  In my case, if I pass parameters Article1, Pallet, TN. It should return = 10x20x5/1000 = 1

  Article2, Layer, TN. It should return = 10x10/1000 = 0.1

  Simple on the paper. But I am struggling to build this recursivity.

  I really need help and will appreciate that you give me some tips.

  thank you in advance

  Dirk

• john.arnott

  SSChampion

  Points: 11882

  More actions

  May 20, 2009 at 7:29 pm

  #998027

  Rather than think procedurally, which is only natural when you see what could be considered a recursive structure, another approach may be a fairly straight-forward use of self-joins. This is practical so long as you are sure of the maximum number of levels of sizes you will deal with. I can imagine your test data being expanded to larger units (shipping containers full of pallets, rail cars carrying shipping containers....). With that in mind, here's the start of a possible solution. The key points here are the cascading relationships of the self joins, which give you your recursion, and the use of the "coalesce" function to allow for various depths of the implied hierarchies. You might un-comment the individual fields in the main SELECT to get a better view of the inputs to the calculation of SUOM_Value.

  --generic UOM conversion

  drop table #UOM_Generic

  Create table #UOM_Generic(

  uom_From char(2),

  uom_To char(2),

  uom_Conv dec(18,8))

  Insert into #uom_Generic

  Select 'KG', 'GM', 1000 union all

  Select 'TN', 'KG', 1000 union all

  Select 'LB', 'KG', 0.45

  --specific UOM conversion per article

  Drop table #UOM_Specific

  Create table #UOM_Specific(

  suom_Article char(10),

  suom_From char(10),

  suom_To char(10),

  suom_Conv dec(18,8))

  Insert into #UOM_Specific

  Select 'Article1', 'Pallet', 'Layer', 10 union all

  Select 'Article1', 'Layer', 'box', 20 union all

  Select 'Article1', 'box', 'KG', 5 union all

  Select 'Article3', 'Pallet', 'Layer', 5 union all

  Select 'Article3', 'Layer', 'box', 15 union all

  Select 'Article3', 'box', 'KG', 7 union all

  Select 'Article5', 'ShipContnr', 'Pallet', 10 union all

  Select 'Article5', 'Pallet', 'Layer', 5 union all

  Select 'Article5', 'Layer', 'box', 18 union all

  Select 'Article5', 'box', 'KG', 10 union all

  Select 'Article4', 'Pallet', 'box', 5 union all

  Select 'Article4', 'box', 'KG', 195 union all

  Select 'Article2', 'Pallet', 'Layer', 20 union all

  Select 'Article2', 'Layer', 'box', 10 union all

  Select 'Article2', 'box', 'KG', 10

  Declare @Parm_Article char(10)

  Declare @Parm_From char(10)

  Declare @Parm_To char(10)

  --set @Parm_Article = 'Article2'

  --set @Parm_From = 'Layer'

  --set @Parm_Article = 'Article1'

  --set @Parm_From = 'Pallet'

  ----set @Parm_To = 'TN'

  --set @Parm_Article = 'Article4'

  --set @Parm_From = 'Pallet'

  --set @Parm_To = 'LB'

  set @Parm_Article = 'Article5'

  set @Parm_From = 'ShipContnr'

  set @Parm_To = 'lb'

  select a1.suom_Article

  ,a1.suom_From

  ,a1.suom_Conv

  * coalesce(a2.suom_Conv,1)

  * coalesce(a3.suom_Conv,1)

  * coalesce(a4.suom_Conv,1)

  / coalesce(g1.uom_Conv,1)

  / coalesce(g2.uom_Conv,1)

  / coalesce(g3.uom_Conv,1)

  / coalesce(g4.uom_Conv,1)

  as SUOM_Value

  -- ,a1.suom_Conv as a1conv

  -- , coalesce(a2.suom_Conv,1) as a2conv

  -- , coalesce(a3.suom_Conv,1) as a3conv

  -- , coalesce(a4.suom_Conv,1) as a4conv

  -- , coalesce(g1.uom_Conv,1) as g1conv

  -- , coalesce(g2.uom_Conv,1) as g2conv

  -- , coalesce(g3.uom_Conv,1) as g3conv

  -- , coalesce(g4.uom_Conv,1) as g4conv

  from #Uom_Specific a1

  left outer join #Uom_Specific a2 on a2.suom_From = a1.suom_To and a2.suom_Article = a1.suom_Article

  left outer join #Uom_Specific a3 on a3.suom_From = a2.suom_To and a3.suom_Article = a2.suom_Article

  left outer join #Uom_Specific a4 on a4.suom_From = a3.suom_To and a4.suom_Article = a3.suom_Article

  left outer join #UOM_Generic g1 on g1.uom_to = a1.suom_to and g1.uom_from = @Parm_to

  left outer join #UOM_Generic g2 on g2.uom_to = a2.suom_to and g2.uom_from = @Parm_to

  left outer join #UOM_Generic g3 on g3.uom_to = a3.suom_to and g3.uom_from = @Parm_to

  left outer join #UOM_Generic g4 on g4.uom_to = a4.suom_to and g4.uom_from = @Parm_to

  where a1.suom_Article = @Parm_Article

  and a1.suom_From = @Parm_From