ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Reads → WITH → WITH Examples
WITH, Publishing Associations with a Recursive Redirect
This example demonstrates the addition REDIRECT of
WITH ASSOCIATIONS where the redirect is made to the current CTE.
Other versions:
7.31 | 7.40 | 7.54
Source Code
DATA selection TYPE demo_join2-d VALUE 'xx'.
cl_demo_input=>request( CHANGING field = selection ).
"Path expressions in main query
WITH
+cte AS ( SELECT FROM demo_cds_publish_assoc_1 AS cds
INNER JOIN demo_join2 AS src1
ON cds~d = src1~d
CROSS JOIN demo_join2 AS src2
FIELDS cds~d AS cds_d,
src1~d AS src1_d,
src1~e AS src1_e,
src1~f AS src1_f,
src2~d AS src2_d,
src2~e AS src2_e,
src2~f AS src2_f
WHERE cds~d = @selection )
WITH ASSOCIATIONS
( cds~\_demo_join2
REDIRECTED TO +cte VIA src1 )
SELECT FROM +cte\_demo_join2 AS demo_join2
FIELDS demo_join2~*
ORDER BY cds_d,
src1_d,
src1_e,
src1_f,
src2_d,
src2_e,
src2_f
INTO TABLE @DATA(resulta).
WITH
+cte AS ( SELECT FROM demo_cds_publish_assoc_1 AS cds
INNER JOIN demo_join2 AS src1
ON cds~d = src1~d
CROSS JOIN demo_join2 AS src2
FIELDS cds~d AS cds_d,
src1~d AS src1_d,
src1~e AS src1_e,
src1~f AS src1_f,
src2~d AS src2_d,
src2~e AS src2_e,
src2~f AS src2_f
WHERE cds~d = @selection )
WITH ASSOCIATIONS
( cds~\_demo_join2
REDIRECTED TO +cte VIA src2 )
SELECT FROM +cte\_demo_join2 AS demo_join2
FIELDS demo_join2~*
ORDER BY cds_d,
src1_d,
src1_e,
src1_f,
src2_d,
src2_e,
src2_f
INTO TABLE @DATA(resultb).
IF NOT (
resulta IS INITIAL AND
resultb IS INITIAL ).
ASSERT resulta <> resultb.
ENDIF.
"Explicite joins in main query
WITH
+cte AS ( SELECT FROM demo_cds_publish_assoc_1 AS cds
INNER JOIN demo_join2 AS src1
ON cds~d = src1~d
CROSS JOIN demo_join2 AS src2
FIELDS cds~d AS cds_d,
src1~d AS src1_d,
src1~e AS src1_e,
src1~f AS src1_f,
src2~d AS src2_d,
src2~e AS src2_e,
src2~f AS src2_f
WHERE cds~d = @selection )
SELECT FROM +cte AS cte_1
INNER JOIN +cte AS cte_2
ON cte_1~cds_d = cte_2~src1_d
FIELDS cte_2~*
ORDER BY cte_2~cds_d,
cte_2~src1_d,
cte_2~src1_e,
cte_2~src1_f,
cte_2~src2_d,
cte_2~src2_e,
cte_2~src2_f
INTO TABLE @DATA(join_resulta).
ASSERT join_resulta = resulta.
WITH
+cte AS ( SELECT FROM demo_cds_publish_assoc_1 AS cds
INNER JOIN demo_join2 AS src1
ON cds~d = src1~d
CROSS JOIN demo_join2 AS src2
FIELDS cds~d AS cds_d,
src1~d AS src1_d,
src1~e AS src1_e,
src1~f AS src1_f,
src2~d AS src2_d,
src2~e AS src2_e,
src2~f AS src2_f
WHERE cds~d = @selection )
SELECT FROM +cte AS cte_1
INNER JOIN +cte AS cte_2
ON cte_1~cds_d = cte_2~src2_d
FIELDS cte_2~*
ORDER BY cte_2~cds_d,
cte_2~src1_d,
cte_2~src1_e,
cte_2~src1_f,
cte_2~src2_d,
cte_2~src2_e,
cte_2~src2_f
INTO TABLE @DATA(join_resultb).
ASSERT join_resultb = resultb.
"Show results of CTEs
SELECT FROM demo_cds_publish_assoc_1 AS cds
INNER JOIN demo_join2 AS src1
ON cds~d = src1~d
CROSS JOIN demo_join2 AS src2
FIELDS cds~d AS cds_d,
src1~d AS src1_d,
src1~e AS src1_e,
src1~f AS src1_f,
src2~d AS src2_d,
src2~e AS src2_e,
src2~f AS src2_f
WHERE cds~d = @selection
INTO TABLE @DATA(ctea).
DATA itab_resulta LIKE resulta.
itab_resulta = VALUE #(
FOR wa2 IN ctea
FOR wa1 IN ctea WHERE ( src1_d = wa2-src1_d )
( cds_d = wa1-cds_d
src1_d = wa1-src1_d
src1_e = wa1-src1_e
src1_f = wa1-src1_f
src2_d = wa1-src2_d
src2_e = wa1-src2_e
src2_f = wa1-src2_f ) ).
SORT itab_resulta BY table_line.
ASSERT itab_resulta = resulta.
SELECT FROM demo_cds_publish_assoc_1 AS cds
INNER JOIN demo_join2 AS src1
ON cds~d = src1~d
CROSS JOIN demo_join2 AS src2
FIELDS cds~d AS cds_d,
src1~d AS src1_d,
src1~e AS src1_e,
src1~f AS src1_f,
src2~d AS src2_d,
src2~e AS src2_e,
src2~f AS src2_f
WHERE cds~d = @selection
INTO TABLE @DATA(cteb).
DATA itab_resultb LIKE resultb.
itab_resultb = VALUE #(
FOR wb2 IN cteb
FOR wb1 IN cteb WHERE ( src2_d = wb2-src1_d )
( cds_d = wb1-cds_d
src1_d = wb1-src1_d
src1_e = wb1-src1_e
src1_f = wb1-src1_f
src2_d = wb1-src2_d
src2_e = wb1-src2_e
src2_f = wb1-src2_f ) ).
SORT itab_resultb BY table_line.
ASSERT itab_resultb = resultb.
cl_demo_output=>new(
)->write( resulta
)->write_text( 'comes from'
)->write( data = ctea name = '+cte1'
)->write( 'INNER JOIN'
)->write( data = ctea name = '+cte2'
)->write( 'ON +cte1~cds_d = +cte2~src1_d'
)->line(
)->write( resultb
)->write_text( 'comes from'
)->write( data = cteb name = '+cte1'
)->write( 'INNER JOIN'
)->write( data = cteb name = '+cte2'
)->write( 'ON +cte1~cds_d = +cte2~src2_d'
)->display( ).
Description
As in the example Publishing Association with
a Redirect, a common table expression +cte accesses the CDS view DEMO_CDS_PUBLISH_ASSOC_1
and publishes its CDS association _demo_join2. Here, however, the redirect
is made to the current common table expression and not to a preceding common table expression. This
expression specifies the target data source demo_join2 of the published CDS
association twice as an additional data source under the names src1 and src2.
- In the first
WITHstatement, the data source is specified usingsrc1.
- In the second
WITHstatement, the data source is specified usingsrc2.
The results of the two reads are different from each other. The following reads again demonstrate which
join instances are created. The different results originate from the different ON
conditions, where the column of src1 and the column of src2 are both evaluated once.