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: 

about outer join

Former Member
0 Kudos
87

hi experts can somebody provide <b>one ex. with coading , how to declare outer join</b> functionality.

regards.

subhasis

1 ACCEPTED SOLUTION

Former Member
0 Kudos
66

hi,

See this simple example.

table name : <b>emp</b>

empno name
a         sasi
b          xxx 
c          yyy

<b>Outer join</b>

*************************

select e~empno e~name 
s~sal into table  int_table
from emp as e
LEFT OUTER JOIN sal
on 
e~empno = s~empno.

if you made outer join (left /right ) the left table kept as it is the

if the condition satisfy the right table entries will fetch else leave it blank

the output will be

a sasi a 1000
b xxx b 2000
c yyy

Rgds

Reshma

5 REPLIES 5

former_member196299
Active Contributor
0 Kudos
66

hi subhasis,

Outer joins basically copnsists of the conpect of union of the set theory .

here you go with an example from SAP documents :

Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT scarrid scarrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid

AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

Reward if helpful !

Regards,

Ranjita

Former Member
0 Kudos
66

Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT scarrid scarrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid

AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

reward points if helpful

Former Member
0 Kudos
66
REPORT  Z_TEST                  .

TABLES: SCUSTOM, SBOOK.

SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY
       SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID
       INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,
             SBOOK-FLDATE, SBOOK-CARRID, SBOOK-CONNID,
             SBOOK-BOOKID)
       FROM SCUSTOM LEFT OUTER JOIN SBOOK
         ON SCUSTOM~ID = SBOOK~CUSTOMID AND
            SBOOK~FLDATE = '19971015'
       ORDER BY SCUSTOM~NAME SBOOK~FLDATE.
  WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,
           SBOOK-FLDATE, SBOOK-CARRID, SBOOK-CONNID,
           SBOOK-BOOKID.
ENDSELECT.

Also check <a href="http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/frameset.htm">http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/frameset.htm</a>

Best Regards,

Sarath

Former Member
0 Kudos
67

hi,

See this simple example.

table name : <b>emp</b>

empno name
a         sasi
b          xxx 
c          yyy

<b>Outer join</b>

*************************

select e~empno e~name 
s~sal into table  int_table
from emp as e
LEFT OUTER JOIN sal
on 
e~empno = s~empno.

if you made outer join (left /right ) the left table kept as it is the

if the condition satisfy the right table entries will fetch else leave it blank

the output will be

a sasi a 1000
b xxx b 2000
c yyy

Rgds

Reshma

Former Member
0 Kudos
66

Hi,

there are two ways : 1. outer join with db tab and 2. with itab.

For db tab:

SELECT - join

Syntax

... [(] {dbtab_left [AS tabalias_left]} | join

{[INNER] JOIN}|{LEFT [OUTER] JOIN}

{dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

Effect

The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of [INNER] JOIN or LEFT [OUTER] JOIN . Depending on the type of join, a join expression can be either an inner ( INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

e.g.

SELECT ccarrname pconnid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON pcarrid = ccarrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON fcarrid = pcarrid

AND fconnid = pconnid ).

For Itab.:

SELECT - cond

Syntax

... [FOR ALL ENTRIES IN itab] WHERE sql_cond ... .

Effect:

The addition WHERE restricts the number of lines included in the result set by the statement SELECT, by using a logical expression sql_cond. The logical expression compares the content of columns in the database with the content of ABAP data objects, or with the content of other columns. You can use the optional addition FOR ALL ENTRIES to compare the content of a column in the database with a component with all lines of a structured internal table itab.

The logical expression sql_cond is either true, false, or unknown. The expression is unknown if one of the columns involved in the database contains a null value and is evaluated with another comparison as IS NULL. A line is only included in the resulting set if the logical expression is true.

Except for columns of type STRING or RAWSTRING, all columns of the database tables or views listed after FROM can be evaluated after the WHERE condition. The columns do not necessarily have to be a part of the resulting set.

Jogdand M B