Join row with "previous row" in ABAP OpenSQL

Question

Boiled down to the basic problem, I have two tables

dberchz1 (e1)
---------------------
belnr   belzeile
1       1
1       2
1       4
1       5
1       7
1       22

dberchz2 (e2/e3)
---------------------
belnr   belzeile    geraet
1       1           A
1       2           A
1       5           B
1       8           A
1       23          A

The following ABAP OpenSQL Query (Netweaver 7.50)

SELECT e1~belnr, e1~belzeile, e2~geraet, e2~belzeile FROM dberchz1 AS e1
INNER JOIN dberchz2 AS e2 ON e1~belnr = e2~belnr AND e1~belzeile <= e2~belzeile
WHERE
e2~belzeile = ( SELECT MIN( e3~belzeile ) FROM dberchz2 AS e3 WHERE e1~belnr = e3~belnr AND e1~belzeile <= e3~belzeile AND Ae3~geraet <> '' )

yields

e1~belnr    e1~belzeile e2~geraet   e2~belzeile
1           1           A           1
1           2           A           2
1           4           B           5
1           5           B           5
1           7           A           8
1          22           A           23

However, what I try to achieve is

e1~belnr    e1~belzeile e2~geraet   e2~belzeile
1           1           A           1
1           2           A           2
1           4           *A*         *2*
1           5           B           5
1           7           *B*         *5*
1          22           A           23

In pseudo code:

Loop through e1 (sorted in ascending ordered by belzeile)
Assign each row of e1 the row of e2 which has the same belzeile.
If no such row exist, assign the "previous" (by belzeile) row.

e2~belzeile is not really needed in the final result but only there for demonstration purposes.

Here is a fiddle: http://sqlfiddle.com/#!9/e22c7d0/1.

Any ideas?

Note that the fiddle allows more than ABAP OpenSQL, i.e. some statements (e.g. most importantly subqueries in join conditions or in select statements or LAG/LEAD functions) are not supported in ABAP OpenSQL. The full ABAP OpenSQL doc with all limitation is here https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abapselect.htm Since it’s a bit daunting to read through it, I’d encourage you to just make good proposals that work in “plain” DB agnostic SQL and I’ll check if they work in ABAP OpenSQL too.

Answer 1

SELECT e1~belnr    AS e1_belnr,
       e1~belzeile AS e1_belzeile,
       e2~geraet   AS e2_geraet,
       e2~belzeile AS e2_belzeile
  FROM dberchz1 AS e1
  LEFT OUTER JOIN dberchz2 AS e2
    ON e1~belnr = e2~belnr
  WHERE e2~belzeile = (
    SELECT MAX( s~belzeile ) AS e2_belzeile
      FROM dberchz2 AS s
      WHERE s~belnr = e1~belnr
        AND s~belzeile <= e1~belzeile )
  INTO TABLE @rt_result.

The final row is actually (1, 22, A, 8), not (1, 22, A, 23), as you join the previous row.

I ignored the additional geraet <> '' you mixed into your statement as you didn't mention it in the textual description; feel free to add it as needed.

Note however, that this looks pretty cumbersome. If possible, I'd advise to write a scripted database view instead.

Validated on a NW 7.52 SP03 system on SAP HANA 2.0.