Skip to content

ABAP Keyword Documentation →  ABAP - Dictionary →  Dependency Rules in ABAP Dictionary →  Dictionary DDL for defining dependency rules →  DEFINE DEPENDENCY RULE 

DEFINE JOIN DEPENDENCY RULE

Other versions: 7.31 | 7.40 | 7.54

Syntax


DEFINE JOIN DEPENDENCY RULE drul 
  ON dbtab1, dbtab2 
    IF { cond_pattern } 
      THEN { derived_cond }

Effect

Variant of the Dictionary DDL statement DEFINE DEPENDENCY RULE used to define a join dependency rule. A join dependency rule can be defined for precisely two database tables. dbtab1 and dbtab2, separated by a comma. It derives an additional join condition derived_cond from the pattern cond_pattern When two database tables joined by a join expression are read, the SAP HANA database optimizer finds the pattern in the ON condition of the expression and, if necessary, adds the additional join condition AND to this condition. Any additional conditions defined by filter dependency rules for the two database rules are also applied to the other database table, if they apply to the columns specified in the join condition.

cond_pattern

In cond_pattern, a pattern from a relational expression can be specified in the curly brackets after IF as follows:

lhs = rhs [AND lhs = rhs ...]

Comparisons with the operator = joined using AND are possible. Joins using OR or negations using NOT are not possible.

  • The following can be specified as the left side lhs of the comparison:
  • dbtab1.column or dbtab2.column for a column column of the database tables dbtab1 or dbtab2.
  • The following can be specified as the right side rhs of the comparison:
  • dbtab1.column or dbtab2.column for a column column of the database tables dbtab1 or dbtab2.
  • A character literal or a numeric literal. This syntax is the same as in literals in ABAP CDS, but without the domain prefix.
  • A numbered placeholder $1, $2, ...

derived_cond

In derived_cond, a template from a relational expression can be specified in the curly brackets after THEN as follows:

lhs =|<|>|<=|>= rhs [OR lhs =|<|>|<=|>= rhs ...]

Single comparisons cannot be placed in parentheses ( ). Negations using NOT are not possible.

  • The following can be specified as the left side lhs of a comparison:
  • dbtab1.column or dbtab2.column for a column column of the database column dbtab2.column or dbtab2.column.
  • The following can be specified as the right side rhs of a comparison:
  • dbtab1.column or dbtab2.column for a column column of the database column dbtab2.column or dbtab2.column.
  • A character literal or a numeric literal. This syntax is the same as in literals in ABAP CDS, but without the domain prefix.
  • A numbered placeholder $1, $2, ..., which must also be specified in cond_pattern. When the condition is added, the operand for the condition is inserted here for which the same placeholder exists in the pattern cond_pattern.


Notes

  • It cannot be guaranteed that the SAP HANA database finds the pattern cond_pattern. This is why the additional condition derived_cond must not modify the results set of the original query. This is technically possible, but produces undefined behavior in cases where the SAP HANA database optimizer finds the pattern cond_pattern in some reads but not in others.

  • The additional condition for a query can be viewed in the SQL Trace tool (transaction ST05) by choosing Display Execution Plan.


Example

The following dependency rules are defined:

DEFINE FILTER DEPENDENCY RULE demo_drul_3
  ON demo_parts_3
    IF { demo_parts_3.value >= 30 }
      THEN { demo_parts_3.area >= 3 }

DEFINE FILTER DEPENDENCY RULE demo_drul_4
  ON demo_parts_3
    IF { demo_parts_3.value < 40 }
      THEN { demo_parts_3.area < 4 }

DEFINE JOIN DEPENDENCY RULE demo_drul_5
  ON demo_parts_3, demo_parts_4
    IF { demo_parts_3.client = demo_parts_4.client AND
         demo_parts_3.id     = demo_parts_4.id     AND
         demo_parts_3.value  = demo_parts_4.value }
      THEN { demo_parts_3.area  = demo_parts_4.area }

The program DEMO_DEPENDENCY_RULE_3 uses the following SELECT statement to access DEMO_PARTS_3 and DEMO_PARTS_4:

SELECT demo_parts_3~id, demo_parts_3~value, demo_parts_4~text
       FROM demo_parts_3
         INNER JOIN demo_parts_4
           ON demo_parts_3~id    = demo_parts_4~id AND
              demo_parts_3~value = demo_parts_4~value
       WHERE demo_parts_3~value >= 30 AND
             demo_parts_3~value <  40
       ORDER BY demo_parts_3~id
       INTO TABLE @DATA(result).

The SAP HANA database optimizer adds the following to the ON condition implicitly:

AND demo_parts_3~area = demo_parts_4~area

It also appends the following addition filter conditions to the WHERE condition implicitly:

AND demo_parts_3~area >= 3
AND demo_parts_3~area <  4
AND demo_parts_4~area >= 3
AND demo_parts_4~area <  4

If the data in the database tables is structured correctly, the dependency rules do not modify the results set.