Skip to content

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

DEFINE FILTER DEPENDENCY RULE

Other versions: 7.31 | 7.40 | 7.54

Syntax


DEFINE FILTER DEPENDENCY RULE drul 
  ON dbtab 
    IF { cond_pattern } 
      THEN { derived_cond }

Effect

Variant of the Dictionary DDL statement DEFINE DEPENDENCY RULE used to define a filter dependency rule. A filter dependency rule can be defined for precisely one database table dbtab. It derives an additional filter condition derived_cond from the pattern cond_pattern When the database table is read, the SAP HANA database optimizer finds the pattern in the WHERE condition and, if necessary, adds the additional filter condition AND to this 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

It is not possible to join multiple expressions using AND or OR or to negate them using NOT.

  • The following can be specified as the left side lhs of the comparison:
  • dbtab.column for a column column of the database table dbtab.
  • The following can be specified as the right side rhs of the comparison:
  • dbtab.column for a column column of the database table dbtab.
  • 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:
  • dbtab.column for the column column of the database table dbtab.
  • The following can be specified as the right side rhs of a comparison:
  • dbtab.column for the column column of the database table dbtab.
  • 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 rule demonstrates how the additional condition works:

DEFINE FILTER DEPENDENCY RULE demo_drul_1
  ON demo_parts_1
    IF { demo_parts_1.value > $1 }
      THEN { demo_parts_1.area = $1 }

On the SAP HANA database, it modifies the results set of every read performed on the database table DEMO_PARTS_1. This is done for demonstration purposes here and should never occur in real situations. The program DEMO_DEPENDENCY_RULE_1uses identical SELECT statements to access the database table DEMO_PARTS_1 with a dependency rule and an identical table, DEMO_PARTS, without a dependency rule:

DATA num TYPE i.
cl_demo_input=>request( CHANGING field = num ).

"Dependency Rule demo_drul_1
SELECT *
       FROM demo_parts_1
       WHERE value > @num
       ORDER BY id
       INTO TABLE @DATA(result1).
cl_demo_output=>write( result1 ).

"No Dependency Rule
SELECT *
       FROM demo_parts
       WHERE value > @num
       ORDER BY id
       INTO TABLE @DATA(result2).
cl_demo_output=>display( result2 ).

When DEMO_PARTS_1 is accessed, the SAP HANA database optimizer evaluates the dependency rule. It finds the pattern

demo_parts_1.value > $1

and adds the additional condition accordingly to create the following logical condition:

WHERE value > @num AND area = @num.

This restricts the results set to those rows in which the column AREA also has the value of num. This restriction does not apply when DEMO_PARTS is accessed.

If the first SELECT statement is transformed into a WITH statement or if alias names are used, for example, the optimizer may not find the pattern and may not add an additional condition. In this case, the result of this example is dependent on the behavior of the optimizer, which should never occur in production programs.


Example

The following filter dependency rule for the database table DEMO_PARTS_2 must not modify the results set. For this to happen, the data in the database table must have the expected values.

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

The program DEMO_DEPENDENCY_RULE_2uses identical SELECT statements to access the database table DEMO_PARTS_2 with a dependency rule and an identical table, DEMO_PARTS, without a dependency rule:


"Dependency Rule demo_drul_2
SELECT *
       FROM demo_parts_2
       WHERE value >= 30  AND value < 40
       ORDER BY id
       INTO TABLE @DATA(result1).

"No Dependency Rule
SELECT *
       FROM demo_parts
       WHERE value >= 30 AND value < 40
       ORDER BY id
       INTO TABLE @DATA(result2).

ASSERT result1 = result2.

If the data in the database table is structured so that in rows where the column VALUE contains values greater than or equal to 30 the values in the column AREA are greater than or equal to 3, the results sets of both reads are identical. To derive an additional condition with a dependency on the condition value < 40, a further filter dependency rule could be defined for the same database table.