topic Re: about outer join in Application Development and Automation Discussions

about outer join

Former Member — Wed, 16 May 2007 05:33:48 GMT

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

regards.

subhasis

Re: about outer join

former_member196299 — Wed, 16 May 2007 05:41:33 GMT

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 s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

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

Re: about outer join

Former Member — Wed, 16 May 2007 06:03:19 GMT

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 s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

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

Re: about outer join

Former Member — Wed, 16 May 2007 06:11:56 GMT

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

Re: about outer join

Former Member — Wed, 16 May 2007 06:14:59 GMT

hi,

See this simple example.

table name : emp

empno name
a         sasi
b          xxx 
c          yyy

Outer join

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

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

Re: about outer join

Former Member — Wed, 16 May 2007 06:15:00 GMT

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 c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

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