Replace correlated subquery with join

Question

I'd like to replace the following ABAP OpenSQL snippet (in the where clause of a much bigger statement) with an equivalent join.

... AND tf~tarifart = ( SELECT MAX( tf2~tarifart ) FROM ertfnd AS tf2 WHERE tf2~tariftyp = e1~tariftyp AND tf2~bis >= e1~bis AND tf2~ab <= e1~ab ) ...

My motivation: Query migration to ABAP CDS views (basically plain SQL with in comparison somewhat reduced expressiveness). Alas, correlated subqueries and EXISTS statements are not supported.

I googled a bit and found a possible solution (last post) here https://archive.sap.com/discussions/thread/3824523

However, the proposal

1. Selecting MAX(value)
2. Your scenarion using inner join to first CDS view

doesn't work in my case.

1. tf.bis (and tf.ab) need to be in the selection list of the new view to limit the rhs of the join (new view) to the correct time frames.
2. Alas, there could be multiple (non overlapping) sub time frames (contained within [tf.ab, tf.bis]) with the same tf.tarifart. Since these couldn't be grouped together, this results in multiple rows on the rhs.

The original query does not have a problem with that (no join -> no Cartesian product).

I hope the following fiddle (working example) clears things up a bit: http://sqlfiddle.com/#!9/8d1f48/3

Given these constraints, to me it seems that an equivalent join is indeed impossible. Suggestions or even confirmations?

Answer 1

select doc_belzart,
       doc_tariftyp,
       doc_ab,
       doc_bis,
       max(tar_tarifart)
  from 
  (
    select document.belzart as doc_belzart, 
           document.tariftyp as doc_tariftyp,  
           document.ab as doc_ab,
           document.bis as doc_bis, 
           tariff.tarifart as tar_tarifart,
           tariff.tariftyp as tar_tariftyp,
           tariff.ab as tar_ab,
           tariff.bis as tar_bis
      from dberchz1 as document
      inner join ertfnd as tariff
        on tariff.tariftyp = document.tariftyp and
           tariff.ab <= document.ab and
           tariff.bis >= document.bis
  ) as max_tariff
  group by doc_belzart,
          doc_tariftyp,
          doc_ab,
          doc_bis

Translated in English, you seem to want to determine the max applicable tariff for a set of documents.

I'd refactor this into separate steps:

1. Determine all applicable tariffs, meaning all tariffs that completely cover the document's time interval. This will become your first CDS view, and in my answer forms the sub-query.

2. Determine for all documents the max applicable tariff. This will form your second CDS view, and in my answer forms the outer query. This one has the MAX / GROUP BY to reduce the result set to one per document.