Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

right outer join

former_member450736
Active Participant
0 Kudos

is right Outer Join allowed in abap? if not why it is not allowed? any special (only)reasons for the same?

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi Kranthi,

check this.

Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins that produce the same results on all of the supported database systems:

Only a table or view may appear to the right of the JOIN operator, not another join expression.

Only AND is possible in the ON condition as a logical operator.

Each comparison in the ON condition must contain a field from the right-hand table.

If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2.

Note

In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below:

Variant 3

... FROM tabref1 LEFT OUTER JOIN tabref2 ON cond

Effect

Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary.

In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table.

Left outer join between table 1 and table 2 where column D in both tables set the join condition:

Table 1 Table 2

-


-


-


-


-


-


-


-


-


A B C D D E F G H

-


-


-


-


-


-


-


-


-


a1 b1 c1 1 1 e1 f1 g1 h1

a2 b2 c2 1 3 e2 f2 g2 h2

a3 b3 c3 2 4 e3 f3 g3 h3

a4 b4 c4 3 -


-


-


-


-


-


-


-


-


\ /

\ /

\ /

\ /

\/

Left Outer Join

-


-


-


-


-


-


-


-


-


A B C D D E F G H

-


-


-


-


-


-


-


-


-


a1 b1 c1 1 1 e1 f1 g1 h1

a2 b2 c2 1 1 e1 f1 g1 h1

a3 b3 c3 2 NULL NULL NULL NULL NULL

a4 b4 c4 3 3 e2 f2 g2 h2

-


-


-


-


-


-


-


-


-


Example

Output a list of all custimers with their bookings for October 15th, 2001:

DATA: CUSTOMER TYPE SCUSTOM,

BOOKING TYPE SBOOK.

SELECT SCUSTOMNAME SCUSTOMPOSTCODE SCUSTOM~CITY

SBOOKFLDATE SBOOKCARRID SBOOKCONNID SBOOKBOOKID

INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK

ON SCUSTOMID = SBOOKCUSTOMID AND

SBOOK~FLDATE = '20011015'

ORDER BY SCUSTOMNAME SBOOKFLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or using an alias.

Note

For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

Only a table or view may come after the JOIN operator, not another join statement.

The only logical operator allowed in the ON condition is AND.

Each comparison in the ON condition must contain a field from the right-hand table.

Comparisons in the WHERE condition must not contain a field from the right-hand table.

The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).

Note

In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the llen in der FROM clause, according to the structure of each table work area. There can be gaps between the table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, as in the following example (not simply by counting the total number of fields).

Example

Example of a JOIN with more than two tables: Select all flights from Frankfurt to New York between September 10th and 20th, 2001 where there are available places, and display the name of the airline.

DATA: BEGIN OF WA,

FLIGHT TYPE SFLIGHT,

PFLI TYPE SPFLI,

CARR TYPE SCARR,

END OF WA.

SELECT * INTO WA

FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

ON FCARRID = PCARRID AND

FCONNID = PCONNID )

INNER JOIN SCARR AS C

ON FCARRID = CCARRID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND FSEATSOCC < FSEATSMAX.

WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,

WA-FLIGHT-CONNID.

ENDSELECT.

Variant 4

... FROM (source_text) AS alias

Effect

Works like variants 1-3, provided the source_text varialb contains the table name or a join expression as ABAP source text.

Examples

Output of a list of all customers:

DATA tabname(10).

DATA: BEGIN OF wa,

id TYPE scustom-id,

name TYPE scustom-name,

END OF wa.

tabname = 'SCUSTOM'.

SELECT id name INTO CORRESPONDING FIELDS OF wa FROM (tabname).

WRITE: / wa-id, wa-name.

ENDSELECT.

Output of all flight connections with the airline name and the flight number where a dynamic join is set up at runtime.

CONSTANTS: flight_tab_name(30) VALUE 'SPFLI'.

DATA: from_clause TYPE STRING.

DATA: BEGIN OF wa,

name(20) TYPE C,

connid TYPE spfli-connid,

END OF wa.

CONCATENATE flight_tab_name ' AS t1'

' JOIN scarr AS t2 ON t1carrid = t2carrid'

INTO from_clause.

SELECT t1connid t2carrname AS name

FROM (from_clause)

INTO CORRESPONDING FIELDS OF wa.

WRITE: / wa-name, wa-connid.

ENDSELECT.

Notes

You can only specify a FROM clause at runtime if you also specify an INTO clause.

If ON conditions are specified in source_text, you have the following limitations:

Only literals, not variables, can be used as values.

The IN operator cann be used in the form f1 IN itab1.

The variable source_text must not be empty.

The AS alias addition may only be used in the form ... FROM (source_text) AS alias if the source_text has neither a join expression nor a table alias.

If you have a statement in the form SELECT * FROM (source_text) INTO wa , no join expression with a table containing strings must be specified in the source_text. However, if you specify a single table, this may contain strings.

When you perform a grammatical analysis of the source text in source_text, the same exceptions can occur as in a dynamic logical condition. In addition, the following exceptions can occur:

kindly reward if found helpful.

cheers,

Hema.

3 REPLIES 3

Former Member
0 Kudos

Hi Kranthi,

check this.

Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins that produce the same results on all of the supported database systems:

Only a table or view may appear to the right of the JOIN operator, not another join expression.

Only AND is possible in the ON condition as a logical operator.

Each comparison in the ON condition must contain a field from the right-hand table.

If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2.

Note

In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below:

Variant 3

... FROM tabref1 LEFT OUTER JOIN tabref2 ON cond

Effect

Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary.

In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table.

Left outer join between table 1 and table 2 where column D in both tables set the join condition:

Table 1 Table 2

-


-


-


-


-


-


-


-


-


A B C D D E F G H

-


-


-


-


-


-


-


-


-


a1 b1 c1 1 1 e1 f1 g1 h1

a2 b2 c2 1 3 e2 f2 g2 h2

a3 b3 c3 2 4 e3 f3 g3 h3

a4 b4 c4 3 -


-


-


-


-


-


-


-


-


\ /

\ /

\ /

\ /

\/

Left Outer Join

-


-


-


-


-


-


-


-


-


A B C D D E F G H

-


-


-


-


-


-


-


-


-


a1 b1 c1 1 1 e1 f1 g1 h1

a2 b2 c2 1 1 e1 f1 g1 h1

a3 b3 c3 2 NULL NULL NULL NULL NULL

a4 b4 c4 3 3 e2 f2 g2 h2

-


-


-


-


-


-


-


-


-


Example

Output a list of all custimers with their bookings for October 15th, 2001:

DATA: CUSTOMER TYPE SCUSTOM,

BOOKING TYPE SBOOK.

SELECT SCUSTOMNAME SCUSTOMPOSTCODE SCUSTOM~CITY

SBOOKFLDATE SBOOKCARRID SBOOKCONNID SBOOKBOOKID

INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK

ON SCUSTOMID = SBOOKCUSTOMID AND

SBOOK~FLDATE = '20011015'

ORDER BY SCUSTOMNAME SBOOKFLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or using an alias.

Note

For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

Only a table or view may come after the JOIN operator, not another join statement.

The only logical operator allowed in the ON condition is AND.

Each comparison in the ON condition must contain a field from the right-hand table.

Comparisons in the WHERE condition must not contain a field from the right-hand table.

The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).

Note

In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the llen in der FROM clause, according to the structure of each table work area. There can be gaps between the table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, as in the following example (not simply by counting the total number of fields).

Example

Example of a JOIN with more than two tables: Select all flights from Frankfurt to New York between September 10th and 20th, 2001 where there are available places, and display the name of the airline.

DATA: BEGIN OF WA,

FLIGHT TYPE SFLIGHT,

PFLI TYPE SPFLI,

CARR TYPE SCARR,

END OF WA.

SELECT * INTO WA

FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

ON FCARRID = PCARRID AND

FCONNID = PCONNID )

INNER JOIN SCARR AS C

ON FCARRID = CCARRID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND FSEATSOCC < FSEATSMAX.

WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,

WA-FLIGHT-CONNID.

ENDSELECT.

Variant 4

... FROM (source_text) AS alias

Effect

Works like variants 1-3, provided the source_text varialb contains the table name or a join expression as ABAP source text.

Examples

Output of a list of all customers:

DATA tabname(10).

DATA: BEGIN OF wa,

id TYPE scustom-id,

name TYPE scustom-name,

END OF wa.

tabname = 'SCUSTOM'.

SELECT id name INTO CORRESPONDING FIELDS OF wa FROM (tabname).

WRITE: / wa-id, wa-name.

ENDSELECT.

Output of all flight connections with the airline name and the flight number where a dynamic join is set up at runtime.

CONSTANTS: flight_tab_name(30) VALUE 'SPFLI'.

DATA: from_clause TYPE STRING.

DATA: BEGIN OF wa,

name(20) TYPE C,

connid TYPE spfli-connid,

END OF wa.

CONCATENATE flight_tab_name ' AS t1'

' JOIN scarr AS t2 ON t1carrid = t2carrid'

INTO from_clause.

SELECT t1connid t2carrname AS name

FROM (from_clause)

INTO CORRESPONDING FIELDS OF wa.

WRITE: / wa-name, wa-connid.

ENDSELECT.

Notes

You can only specify a FROM clause at runtime if you also specify an INTO clause.

If ON conditions are specified in source_text, you have the following limitations:

Only literals, not variables, can be used as values.

The IN operator cann be used in the form f1 IN itab1.

The variable source_text must not be empty.

The AS alias addition may only be used in the form ... FROM (source_text) AS alias if the source_text has neither a join expression nor a table alias.

If you have a statement in the form SELECT * FROM (source_text) INTO wa , no join expression with a table containing strings must be specified in the source_text. However, if you specify a single table, this may contain strings.

When you perform a grammatical analysis of the source text in source_text, the same exceptions can occur as in a dynamic logical condition. In addition, the following exceptions can occur:

kindly reward if found helpful.

cheers,

Hema.

Former Member
0 Kudos

RIGHT OUTER JOIN is one of the JOIN operations that allow you to specify a JOIN clause. It preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table. A LEFT OUTER JOIN B is equivalent to B RIGHT OUTER JOIN A, with the columns in a different order.

The scope of expressions in the ON clause includes the current tables and any tables in query blocks outer to the current SELECT. The ON clause can reference tables not being joined and does not have to reference either of the tables being joined (though typically it does).

Example:

get all countries and corresponding cities, including

countries without any cities

SELECT CITY_NAME, CITIES.COUNTRY

FROM CITIES RIGHT OUTER JOIN COUNTRIES

ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;

get all countries in Africa and corresponding cities, including

countries without any cities

SELECT CITY_NAME, CITIES.COUNTRY

FROM CITIES RIGHT OUTER JOIN COUNTRIES

ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;

WHERE Countries.region = 'frica';

use the synonymous syntax, RIGHT JOIN, to achieve exactly

the same results as in the example above

SELECT CITY_NAME, CITIES.COUNTRY

FROM CITIES RIGHT JOIN COUNTRIES

ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE

WHERE Countries.region = 'Africa';

a TableExpression can be a joinOperation. Therefore

you can have multiple join operations in a FROM clause

List every employee number and last name

with the employee number and last name of their manager

SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME

FROM EMPLOYEE E RIGHT OUTER JOIN

DEPARTMENT RIGHT OUTER JOIN EMPLOYEE M

ON MGRNO = M.EMPNO

ON E.WORKDEPT = DEPTNO;

Also have a look at below link.

http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm

Former Member
0 Kudos

Hi,

Right Outer Join is allowed in ABAP

RIGHT OUTER JOIN: Retrieves all values with or without matching with another table records. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

Regards,

Satish