Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL path expressions sql_path 

ABAP SQL - Path Expressions, attributes

Other versions: 7.31 | 7.40 | 7.54

Syntax


... [ [(n|*) [INNER|{LEFT|RIGHT OUTER}]] [[WHERE]
sql_cond] ] ...

Extras

1. ... (n|*)

2. ... INNER|{LEFT|RIGHT OUTER}

3. ... [WHERE] sql_cond

Effect

Attributes for a section of a path expression can be specified in square brackets for every CDS association or CTE association _assoc of the path expression specified in a data source of a FROM clause or a column specification The following can be done using these attributes:

  • The cardinality of the section can be declared
  • The type of the join expression can be specified
  • A filter condition sql_cond can be specified

Addition 1

... (n|*)

Effect

The cardinality of the current association _assoc is declared by specifying a number n or the character is specified in parentheses ( ). The numbers 1 and 2 can be specified for n. If specified, the cardinality overwrites the definition of the cardinality [min..max] or TO ONE|MANY of the current association with "to 1", "to 2", or "to ".

  • The cardinality can be specified to prevent syntax warnings or syntax errors in cases where the cardinality of the association does not match the way it is used in a path expression of a SELECT statement
  • If (1) is specified, a LEFT OUTER JOIN is defined implicitly using the addition MANY TO ONE (on database systems that support this) and the consequences of this should be noted.


Note

If the cardinality is specified in a path expression, the syntax check is performed in strict mode from Release 7.52.


Example

Paths specified with an explicitly specified cardinality of CDS associations in the SELECT list. If supported by the database, only the left outer join between the database tables SPFLI and SAIRPORT is defined using the addition MANY TO ONE.

SELECT scarr~carrname, 
       \_spfli[ (*) ]-connid AS connid, 
       \_spfli[ (*) ]\_sflight[ (*) ]-fldate AS fldate, 
       \_spfli[ (*) ]\_sairport[ (1) ]-name AS name 
       FROM demo_cds_assoc_scarr AS scarr 
       WHERE scarr~carrid = '...' 
       ORDER BY carrname, connid, fldate 
       INTO TABLE @DATA(result).

Addition 2

... INNER|{LEFT|RIGHT OUTER}

Effect

This addition defines the type of join into which the current association _assoc is transformed:

  • INNER is an inner join
  • LEFT|RIGHT OUTER produces a left or right outer join

The type of the join expression can only be specified together with the cardinality.

If the type of join expression is not specified explicitly, the type depends on the place where the path expression is used:


Note

If the type of the join is specified in a path expression, the syntax check is performed in strict mode from Release 7.52.


Example

Paths specified with an explicitly specified cardinality and and a switch of the left outer joins to inner joins in the columns specified in the SELECT list.

SELECT scarr~carrname, 
       \_spfli[ (1) INNER ]-connid AS connid, 
       \_spfli[ (1) INNER ]\_sflight[ (1) INNER ]-fldate AS fldate, 
       \_spfli[ (1) INNER ]\_sairport[ (1) INNER ]-name AS name 
       FROM demo_cds_assoc_scarr AS scarr 
       WHERE scarr~carrid = 'LH' 
       ORDER BY carrname, connid, fldate 
       INTO TABLE @DATA(result).ABAP_EXAMPLE

Addition 3

...  [WHERE] sql_cond

Effect

Specifies a filter condition sql_cond for the current association _assoc. The addition WHERE is optional in cases where the filter condition is the only item specified in the square brackets. The addition must be specified if one of the other additions is used first.

When the association is resolved in a join, the filter condition is converted to an extended condition for the join. If no filter condition is specified in the path expression, the default filter condition defined using WITH DEFAULT FILTER is used in the case of a CDS association.

Columns specified in the filter condition sql_cond always refer to the targets target or target of the association for which the condition is specified. An explicit name must not and cannot be specified with the column selector ~ before a column specification.


Notes


Example

Specifying filter conditions in a path expression.

SELECT carrid, connid, fldate, price 
       FROM demo_cds_assoc_scarr 
            \_spfli[   airpfrom = 'FRA' ] 
            \_sflight[ currency  = 'EUR' AND 
                       price  BETWEEN 500 AND 1500 ] 
            AS flights 
       ORDER BY carrid, connid, fldate, price 
       INTO TABLE @DATA(result).ABAP_EXAMPLE