title: How to do conversion of units between two tables in SAP HANA Database
body: <p>Hi I stumble upon some problem with <strong>SAP HANA database please note</strong>. I have some toy example problem. I tried to come up with solution, but I cannot figure out.
  I have two tables one of <strong>Volume table</strong> </p>
  
  <pre><code>Row MATERIAL    BASE_UOM    PERIOD  SUMMAND
  1   55555        KG         201106     99
  2   55555        KG         201204     16
  3   55555        KG         201301     71
  4   55555        KG         201411     83
  5   55555        KG         201509     21
  6   55555        PAL        201510     64
  7   55555        PAL        201511     69
  8   55555        KG         201512     55
  9   55555        ZRS        201601     81
  10  55555        KG         201602     3
  11  55555        KG         201603     74
  12  55555        KG         201604     58
  13  55555        ZRS        201605     8
  14  55555        KG         201606     22
  </code></pre>
  
  <p>The second table is <strong>Conversion Table</strong> </p>
  
  <pre><code>Row MATERIAL    UNIT    BASE_UOM    UOMN1D     UOMZ1D    Conversion Rate
  1   55555        KG        KG        1          1            1
  2   55555        CSE       KG        1,000.00   2,016.00    2.016
  3   55555        ST        KG        1,000.00   56          0.056
  4   55555        LAY       KG        100        3,024.00    30.24
  5   55555        PAL       KG        100        54,432.00   544.32
  6   55555        ZDK       KG        59,778.00  54,432.00   0.910569106
  7   55555        ZSE       KG        59,778.00  54,432.00   0.910569106
  8   55555        ZNO       KG        59,778.00  54,432.00   0.910569106
  9   55555        ZFI       KG        59,778.00  54,432.00   0.910569106
  10  55555        ZRV       KG        1,000.00   57          0.057
  11  55555        ZRS       KG        1,000.00   2,052.00    2.052
  </code></pre>
  
  <p><em>Please bear in mind that Row column is indicative!</em></p>
  
  <p><strong>Key between tables is MATERIAL column.</strong></p>
  
  <p>I would like to convert all fields in column <strong>SUMMAND (Volume table)</strong>, when there are not KG in <strong>BASE_UOM</strong> column <strong>Volume table</strong> e.g. in row 6 is PAL into KG or row 13 ZRS into KG. If we are in situation that we have KG we do not need to update column <strong>SUMMAND</strong>. To convert PAL into KG I need to look up at <strong>Conversion Table</strong> at  <strong>MATERIAL</strong> and <strong>UNIT</strong> Column (look up PAL field) and get row 5 from <strong>Conversion Rate</strong> column (<strong>UOMZ1D/UOMN1D</strong>) 544.32*64 (64 is coming from <strong>Volume table</strong> row 6 Column <strong>SUMMAND</strong>) and so on.  Finally, each individual <strong>MATERIAL</strong> has its own conversion rate.</p>
  
  <p>The final output of <strong>Volume table</strong> should look like this. Row 6,7,9,13 is updated correctly.</p>
  
  <pre><code>Row MATERIAL    BASE_UOM    PERIOD  SUMMAND
  1   55555        KG         201106    99
  2   55555        KG         201204    16
  3   55555        KG         201301    71
  4   55555        KG         201411    83
  5   55555        KG         201509    21
  6   55555        PAL        201510    34836.48
  7   55555        PAL        201511    37558.08
  8   55555        KG         201512    55
  9   55555        ZRS        201601    166.212
  10  55555        KG         201602    3
  11  55555        KG         201603    74
  12  55555        KG         201604    58
  13  55555        ZRS        201605    16.416
  14  55555        KG         201606    22
  </code></pre>
  
  <p>How I can check for every possible combination to convert all units into KG between this two tables. Would like to suggest me how to scale it, if I have longer list conversion between UNIT and BASE_UOM from <strong>Conversion Table</strong> imagine like 90 unique possible combinations. What SQL query I need to build in order to achieve the desired output. Any kind of solution are welcome.</p>