Ansicht
Dokumentation

ABENDDICDDL_DEFINE_JOIN_DRUL - DDICDDL DEFINE JOIN DRUL

ABAP Short Reference   General Material Data  
This documentation is copyright by SAP AG.

- DEFINE JOIN DEPENDENCY RULE

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 exactly 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 DDIC 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 DDIC 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 DDIC 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 DDIC database tables dbtab1 or dbtab2.

• A character literal or a numeric literal. This syntax is the same as in literals in ABAP CDS.

• 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.

• 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.

• Built-in functions in ABAP Dictionary in ABAP CDS syntax.

• Simple case distinctions in ABAP CDS syntax.

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 result 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 DDIC database tables is structured correctly, the dependency rules do not modify the result set.

ROGBILLS - Synchronize billing plans   ABAP Short Reference  
This documentation is copyright by SAP AG.

Length: 11290 Date: 20240426 Time: 142834     sap01-206 ( 114 ms )