title: HANA SQL: How to join to first row under a condition refering to main table
body: <p>I will use a hypothetical example to illustrate my problem of joining two tables on which I have been stuck for quite a while now:</p>
  
  <p>Each <strong>Order</strong> has many delivery dates and quantities:</p>
  
  <p><strong>Order</strong>:</p>
  
  <pre><code>OrderID   DeliveryID  DeliveryDate     Quantity
  =======   ==========  ============     ========
  1000      001         2017-01-01       10
  1000      002         2017-01-08       10
  1000      003         2017-01-15       10
  1001      001         ....             10
  </code></pre>
  
  <p>The <strong>received goods</strong> are mapped to an OrderID, but not DeliveryID:</p>
  
  <p><strong>Received</strong>:</p>
  
  <pre><code>OrderID   InvoiceID   ReceivedDate   ReceivedQuantity
  =======   =========   ============   ================
  1000      1001        2017-01-01     10 
  1000      1002        2017-01-09     09
  1000      1003        2017-01-10     01
  1000      1004        2017-01_15     10
  </code></pre>
  
  <p>I am now trying to join the Received table to my Order table. But since, for example, the second order is received in two seperate steps, I want to join the order to the latter received-line, since only then my order has fully arrived.</p>
  
  <p>By joining normally via OrderID we would receive, with the upper example of 3 Orders and 4 Receivings, a total of 12 joined items.</p>
  
  <p>So I want to solve this issue by accumulating the quantity for each OrderID and for each Delivery with respect to the Order ID, with my adjusted tables looking like this:</p>
  
  <p><strong>Order</strong></p>
  
  <pre><code>OrderID   DeliveryID  DeliveryDate     Quantity   OrderedAccumQuant
  =======   ==========  ============     ========   ================
  1000      001         2017-01-01       10         10
  1000      002         2017-01-08       10         20
  1000      003         2017-01-15       10         30
  1001      001         ....             10         10
  </code></pre>
  
  <p><strong>Received</strong>:</p>
  
  <pre><code>OrderID   InvoiceID   ReceivedDate   ReceivedQuantity   AccumQuant
  =======   =========   ============   ================   ================
  1000      1001        2017-01-01     10                 10
  1000      1002        2017-01-09     09                 19
  1000      1003        2017-01-10     01                 20
  1000      1004        2017-01_15     10                 30
  </code></pre>
  
  <p>My planned logic is now to join the Received table to the Order table with the first Received-line that has an equal or greater ReceivedAccumQuant than OrderedAccumQuant. In this example, InvoiceID 1002 is not joined since its delivered quantity is not the ordered quantity. </p>
  
  <p><strong>Desired output:</strong></p>
  
  <pre><code>                                          Ordered                      Rec
  OrderID   DelivID  DeliveryDate   Quant   AccQuant  RecDate InvoiceID  AccQu
  =======   =======  ============   =====   =======   ======= =========  =====
  1000      001      2017-01-01     10      10        .-01-01 1001       10
  1000      002      2017-01-08     10      20        .-01-10 1003       20
  1000      003      2017-01-15     10      30        .-01-15 1004       30
  1001      001      ....                
  </code></pre>
  
  <p>The desired output would show me within each OrderID: a match from all DeliveryIDs to an InvoiceID where the received_accumulated_quantity is >= than the ordered_accumulated_quantity</p>
  
  <p><strong>My two approaches:</strong></p>
  
  <p><strong><em>Memory allocation problem</em></strong></p>
  
  <p>One approach was to Inner Join the Received table via a subquery and then rank() the joined table after partiioning by OrderID and DeliveryID and also having included the WHERE condition that Delivery.Cum_Quant >= Order.Cum_Quant. With the outermost WHERE condition of CUM_RANK = 1 we filter for only the first entry per DeliveryID that fulfills the condition.</p>
  
  <p>This solution works well for a small part of my dataset, but this crashes as soon as I use the full dataset due to memory allocation, since many Received Items are being joined to the Order Items before being filtered by the CUM_RANK of 1. With about 5 million planned delivery dates and 5 million receiving dates and up to 100 of deliveries per OrderID the joined table becomes incredibly large :</p>
  
  <pre><code>SELECT 
    *
    ,RANK() OVER(PARTITION BY Received.OrderID, Received.DeliveryID ORDER BY Received.CUM_QUANT) as CUM_RANK
  FROM Orders
  JOIN 
    (
    SELECT  
      *
      ,RANK() OVER(PARTITION BY Received.OrderID ORDER BY ReceivedDate) AS Rank
      ,SUM(QUANTITY) OVER(PARTITION BY Received.OrderID ORDER BY ReceivedDate) AS Cum_Quant
    FROM Received
    )
  ON Orders.OrderID =  Delivery.OrderID
  WHERE
    Received.Cum_Quant &gt;= Order.Cum_Quant
  ORDER BY Orders.OrderID, Received.Cum_Quant
  )
  WHERE CUM_RANK = 1;
  </code></pre>
  
  <p>My error message being the following:
      SAP DBTech JDBC: [4]: cannot allocate enough memory: please check traces for further information </p>
  
  <p>Any ideas on how to approach this?</p>
  
  <p><strong><em>Access to main-table problem</em></strong></p>
  
  <p>My other guess was to do the comparison of Received.AccumQuant and Orders.AccumQuant within a SELECT subquery of the JOIN statement, so that we avoid the many joins being performed. But from within the JOIN statement you cannot access the Orders table:</p>
  
  <pre><code>SELECT *
  FROM Orders
  JOIN (
    SELECT * FROM (
  
      SELECT
      *
      ,ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY ReceivedDate ASC) AS RowNumb
      FROM Delivery 
      WHERE 
      WHERE Orders.OrderID = Received.OrderID 
      AND Received.AccumQuant &gt;= Orders.AccumQuant --(this point doesnt worke since we cannot access the Orders table from here)
      ) AS DeliveryRanked
    ON Orders.OrderID = Received.OrderID
  </code></pre>