topic Re: OUTER JOIN in Application Development and Automation Discussions

OUTER JOIN

Former Member — Wed, 16 May 2007 10:18:21 GMT

could u give information about outer join and please give the example?

how many times, break even point used in the program?

Re: OUTER JOIN

Former Member — Wed, 16 May 2007 10:21:16 GMT

Hi,

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.

Also check http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/frameset.htm

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.

Regards,

Priyanka.

Re: OUTER JOIN

Former Member — Wed, 16 May 2007 10:22:26 GMT

Hello,

The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view.

With an outer join, records are also selected for which there is no entry in some of the tables used in the view.

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.

Syntax

how many times, break even point used in the program?
I guess u want to know how many Breakpoints can be set ?
If this was the intended question then the answer is

Regards,

Deepu.K

Re: OUTER JOIN

former_member196299 — Wed, 16 May 2007 10:22:38 GMT

hi,

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.

there is no limit is using the break points in your code ..

Regards,

Ranjita

Re: OUTER JOIN

Former Member — Wed, 16 May 2007 10:23:32 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

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:

Select * from dbtable

FOR all entries in itab

where db_fld = itab-fld..

Jogdand M B

Re: OUTER JOIN

Former Member — Wed, 16 May 2007 10:38:29 GMT

Hi,

With the use of outer join you can join the tables even there is no entry in all the tables used in the view. In case of inner join there should be an entry in al the tables use in the view.

Hope this helps.

Reward if helpful.

Regards,

Sipra