ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → ABAP SQL → ABAP SQL - Reads → WITH → WITH Examples
WITH, Publishing Associations with a Redirect
This example demonstrates the addition REDIRECT of WITH ASSOCIATIONS for
common table expressions.
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
+cte1 AS ( SELECT d, e, f
FROM demo_join2
WHERE d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
WITH ASSOCIATIONS
( demo_cds_publish_assoc_1~\_demo_join2
REDIRECTED TO +cte1 VIA demo_join2 )
SELECT demo_join2~*
FROM +cte2\_demo_join2 AS demo_join2
ORDER BY d, e, f
INTO TABLE @DATA(result1).
WITH
+cte1 AS ( SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src1~d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
WITH ASSOCIATIONS
( demo_cds_publish_assoc_1~\_demo_join2
REDIRECTED TO +cte1 VIA src1 )
SELECT demo_join2~*
FROM +cte2\_demo_join2 AS demo_join2
ORDER BY src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
INTO TABLE @DATA(result2a).
WITH
+cte1 AS ( SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src1~d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
WITH ASSOCIATIONS
( demo_cds_publish_assoc_1~\_demo_join2
REDIRECTED TO +cte1 VIA src2 )
SELECT demo_join2~*
FROM +cte2\_demo_join2 AS demo_join2
ORDER BY src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
INTO TABLE @DATA(result2b).
WITH
+cte1 AS ( SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src2~d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
WITH ASSOCIATIONS
( demo_cds_publish_assoc_1~\_demo_join2
REDIRECTED TO +cte1 VIA src1 )
SELECT demo_join2~*
FROM +cte2\_demo_join2 AS demo_join2
ORDER BY src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
INTO TABLE @DATA(result3a).
WITH
+cte1 AS ( SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src2~d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
WITH ASSOCIATIONS
( demo_cds_publish_assoc_1~\_demo_join2
REDIRECTED TO +cte1 VIA src2 )
SELECT demo_join2~*
FROM +cte2\_demo_join2 AS demo_join2
ORDER BY src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
INTO TABLE @DATA(result3b).
IF NOT (
result2a IS INITIAL AND
result2b IS INITIAL AND
result3a IS INITIAL AND
result3b IS INITIAL ).
ASSERT result2a <> result2b.
ASSERT result2a <> result3a.
ASSERT result2a <> result3b.
ASSERT result2b <> result3a.
ASSERT result2b <> result3b.
ASSERT result3a <> result3b.
ENDIF.
"Explicite joins in main query
WITH
+cte1 AS ( SELECT d, e, f
FROM demo_join2
WHERE d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
SELECT +cte1~d, +cte1~e, +cte1~f
FROM +cte2
INNER JOIN +cte1
ON +cte2~d = +cte1~d
ORDER BY +cte1~d, +cte1~e, +cte1~f
INTO TABLE @DATA(join_result1).
ASSERT join_result1 = result1.
WITH
+cte1 AS ( SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src1~d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
SELECT src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
FROM +cte2
INNER JOIN +cte1
ON +cte2~d = +cte1~src1_d
ORDER BY src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
INTO TABLE @DATA(join_result2a).
ASSERT join_result2a = result2a.
WITH
+cte1 AS ( SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src1~d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
SELECT src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
FROM +cte2
INNER JOIN +cte1
ON +cte2~d = +cte1~src2_d
ORDER BY src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
INTO TABLE @DATA(join_result2b).
ASSERT join_result2b = result2b.
WITH
+cte1 AS ( SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src2~d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
SELECT src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
FROM +cte2
INNER JOIN +cte1
ON +cte2~d = +cte1~src1_d
ORDER BY src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
INTO TABLE @DATA(join_result3a).
ASSERT join_result3a = result3a.
WITH
+cte1 AS ( SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src2~d = @selection ),
+cte2 AS ( SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1 )
SELECT src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
FROM +cte2
INNER JOIN +cte1
ON +cte2~d = +cte1~src2_d
ORDER BY src1_d, src1_e, src1_f, src2_d, src2_e, src2_f
INTO TABLE @DATA(join_result3b).
ASSERT join_result3b = result3b.
"Replace CTEs with internal tables
SELECT d, e, f
FROM demo_join2
WHERE d = @selection
INTO TABLE @DATA(cte1_1).
SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1
INTO TABLE @DATA(cte2_1).
DATA itab_result1 LIKE result1.
itab_result1 = VALUE #(
FOR wa2_1 IN cte2_1
FOR wa1_1 IN cte1_1 WHERE ( d = wa2_1-d )
( d = wa1_1-d e = wa1_1-e f = wa1_1-f ) ).
SORT itab_result1 BY table_line.
ASSERT itab_result1 = result1.
SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src1~d = @selection
INTO TABLE @DATA(cte1_2a).
SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1
INTO TABLE @DATA(cte2_2a).
DATA itab_result2a LIKE result2a.
itab_result2a = VALUE #(
FOR wa2_2a IN cte2_2a
FOR wa1_2a IN cte1_2a WHERE ( src1_d = wa2_2a-d )
( src1_d = wa1_2a-src1_d
src1_e = wa1_2a-src1_e
src1_f = wa1_2a-src1_f
src2_d = wa1_2a-src2_d
src2_e = wa1_2a-src2_e
src2_f = wa1_2a-src2_f ) ).
SORT itab_result2a BY table_line.
ASSERT itab_result2a = result2a.
SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src1~d = @selection
INTO TABLE @DATA(cte1_2b).
SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1
INTO TABLE @DATA(cte2_2b).
DATA itab_result2b LIKE result2b.
itab_result2b = VALUE #(
FOR wa2_2b IN cte2_2b
FOR wa1_2b IN cte1_2b WHERE ( src2_d = wa2_2b-d )
( src1_d = wa1_2b-src1_d
src1_e = wa1_2b-src1_e
src1_f = wa1_2b-src1_f
src2_d = wa1_2b-src2_d
src2_e = wa1_2b-src2_e
src2_f = wa1_2b-src2_f ) ).
SORT itab_result2b BY table_line.
ASSERT itab_result2b = result2b.
SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src2~d = @selection
INTO TABLE @DATA(cte1_3a).
SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1
INTO TABLE @DATA(cte2_3a).
DATA itab_result3a LIKE result3a.
itab_result3a = VALUE #(
FOR wa2_3a IN cte2_3a
FOR wa1_3a IN cte1_3a WHERE ( src1_d = wa2_3a-d )
( src1_d = wa1_3a-src1_d
src1_e = wa1_3a-src1_e
src1_f = wa1_3a-src1_f
src2_d = wa1_3a-src2_d
src2_e = wa1_3a-src2_e
src2_f = wa1_3a-src2_f ) ).
SORT itab_result3a BY table_line.
ASSERT itab_result3a = result3a.
SELECT FROM demo_join2 AS src1
CROSS JOIN demo_join2 AS src2
FIELDS 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 src2~d = @selection
INTO TABLE @DATA(cte1_3b).
SELECT demo_cds_publish_assoc_1~d
FROM demo_cds_publish_assoc_1
INTO TABLE @DATA(cte2_3b).
DATA itab_result3b LIKE result3b.
itab_result3b = VALUE #(
FOR wa2_3b IN cte2_3b
FOR wa1_3b IN cte1_3b WHERE ( src2_d = wa2_3b-d )
( src1_d = wa1_3b-src1_d
src1_e = wa1_3b-src1_e
src1_f = wa1_3b-src1_f
src2_d = wa1_3b-src2_d
src2_e = wa1_3b-src2_e
src2_f = wa1_3b-src2_f ) ).
SORT itab_result3b BY table_line.
ASSERT itab_result3b = result3b.
cl_demo_output=>new(
)->write( result1
)->write_text( 'comes from'
)->write( data = cte2_1 name = '+cte2'
)->write( 'INNER JOIN'
)->write( data = cte1_1 name = '+cte1'
)->write( 'ON +cte2~d = +cte1~d'
)->line(
)->write( result2a
)->write_text( 'comes from'
)->write( data = cte2_2a name = '+cte2'
)->write( 'INNER JOIN'
)->write( data = cte1_2a name = '+cte1'
)->write( 'ON +cte2~d = +cte1~src1_d'
)->line(
)->write( result2b
)->write_text( 'comes from'
)->write( data = cte2_2b name = '+cte2'
)->write( 'INNER JOIN'
)->write( data = cte1_2b name = '+cte1'
)->write( 'ON +cte2~d = +cte1~src2_d'
)->line(
)->write( result3a
)->write_text( 'comes from'
)->write( data = cte2_3a name = '+cte2'
)->write( 'INNER JOIN'
)->write( data = cte1_3a name = '+cte1'
)->write( 'ON +cte2~d = +cte1~src1_d'
)->line(
)->write( result3b
)->write_text( 'comes from'
)->write( data = cte2_3b name = '+cte2'
)->write( 'INNER JOIN'
)->write( data = cte1_3b name = '+cte1'
)->write( 'ON +cte2~d = +cte1~src2_d'
)->display( ).
Description
The common table expression +cte2 accesses a CDS view using the following source code:
define view demo_cds_publish_assoc_1
as select from
demo_join1
association to demo_join2 as _demo_join2 on
_demo_join2.d = demo_join1.d
{
_demo_join2,
demo_join1.d
}
This example demonstrates various redirects of the target data source of the CDS association demo_cds_publish_assoc_1~_demo_join2
published by +cte2 to a preceding common table expression +cte1:
- The first
WITHstatement shows a simple example in which the target data sourcedemo_join2of the CDS association_demo_join2published by+cte2occurs once under its name in+cte1. Onlydemo_join2can be specified afterVIA. When the path expression+cte2\_demo_join2is accessed, an instance of an inner join is created between the results sets of+cte2and+cte1whoseONcondition compares the columns of the results sets defined by the CDS association.
- The next
WITHstatements show examples in which the target data sourcedemo_join2of the CDS association_demo_join2published by+cte2occurs twice as a data source in+cte1under different names,src1andsrc2.
src1is specified afterVIAin the redirect of the firstWITHstatement. TheONcondition of the join instance created when the path expression is accessed uses the column of the results set determined bysrc1.
src2is specified afterVIAin the redirect of the secondWITHstatement. TheONcondition of the join instance created when the path expression is accessed uses the column of the results set determined bysrc2.
- The next two
WITHstatements repeat the preceding statements with a differentWHEREcondition in+cte1, which then produces different results.
The results of all reads are different from each other. The following reads demonstrate which join instances are created:
- First, the same
WITHstatements are executed as above, but the CDS association is not published. Instead, the inner join between the two common table expressions is shown explicitly. More specifically, the difference between theONconditions +cte2~d = +cte1~src1_d andcte2~d = +cte1~src2_dshould be noted, which demonstrates the effect of the data sources specified usingVIA.
- The subqueries of the
WITHstatements are then executed as separate SELECT statements to save their results in internal tables for display purposes. Joins are also realized from internal tables using the value operatorVALUE.
The example ensures that the results of associated reads also match each other.