topic Re: JOINS in Application Development and Automation Discussions

JOINS

Former Member — Tue, 11 Sep 2007 09:55:18 GMT

Hi,

I need example of all types of joins

Re: JOINS

Former Member — Tue, 11 Sep 2007 09:57:46 GMT

go through these links

http://help.sap.com/saphelp_46c/helpdata/EN/50/a71ec8f65911d296390000e82de14a/content.htm

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm

regards

Shiva

Re: JOINS

Former Member — Tue, 11 Sep 2007 09:59:05 GMT

inner join

select a~pernr

a~bukrs

b~vorna

into table itab

from pa0001 as a join pa0002 as b

on a_{pernr = b}pernr

where pernr in s_pernr.

Regards

Vasu

Re: JOINS

Former Member — Tue, 11 Sep 2007 09:59:30 GMT

go to transaction ABAPDOCU

Re: JOINS

Former Member — Tue, 11 Sep 2007 09:59:47 GMT

Inner join

IF p_bsart IS INITIAL.

SELECT ekko~bukrs

ekko~lifnr

ekko~ebeln

ekko~waers

ekko~bsart

ekko~ekorg

ekko~ekgrp

ekpo~ebelp

ekpo~txz01

ekpo~matnr

ekpo~werks

ekpo~menge

ekpo~meins

ekpo~netpr

ekpo~netwr

INTO TABLE t_itab1 FROM

ekko INNER JOIN ekpo ON ekko_{ebeln = ekpo}ebeln

WHERE ekko~ebeln IN s_ebeln AND

ekko~bukrs IN s_bukrs AND

ekko~lifnr IN s_lifnr AND

ekko~ekorg IN s_ekorg AND

ekko~ekgrp IN s_ekgrp AND

ekpo~matnr IN s_matnr.

The difference between an INNER JOIN and an OUTER JOIN is the following. If a query on an INNER JOIN of VBAK (outer table) and VBAP (inner table) finds a record in VBAK but no matching records in VBAP, then no data is retrieved from the database because the inner table is empty. If you still want to keep VBAK rows for which there are no matching VBAP rows, you need to use the OUTER JOIN construct available in ABAP/4 Open SQL in 4.x..

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.

On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.

AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.

The syntax of the join conditions join_cond is the same as that of the sql_cond conditions after the addition WHERE, with the following differences:

At least one comparison must be specified after ON.

Individual comparisons may be joined using AND only.

All comparisons must contain a column in the database table or the view dbtab_right on the right-hand side as an operand.

The following language elements may not be used: BETWEEN, LIKE, IN.

No sub-queries may be used.

For outer joins, only equality comparisons (=, EQ) are possible.

If an outer join occurs after FROM, the join condition of every join expression must contain at least one comparison between columns on the left-hand and the right-hand side.

In outer joins, all comparisons that contain columns as operands in the database table or the view dbtab_right on the right-hand side must be specified in the corresponding join condition. In the WHERE condition of the same SELECT command, these columns are not allowed as operands.

Resulting set for inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

Resulting set for outer join

The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

Example

Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

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 ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Example

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.

Re: JOINS

Former Member — Tue, 11 Sep 2007 10:00:18 GMT

hi,

check this thread...

cheers

Alfred

Re: JOINS

Former Member — Tue, 11 Sep 2007 10:01:10 GMT

INNER JOIN results are an intersection of the tables being joined where in only if both the tables havethe data the result is pused onto the result set.

WHERE as in LEFT OUTR JOIN you can push the data of the LEF T table on the resultset even when the join condition is not met.

The use is that you wantto have all the data that is there in left table and also the right table if the join condition is a success then right table fileds will have data else they are initial.

OUTJOIN's are used in MAINTENANNCE VIEWS, HELP VIEWS .

INNER JOINS are used DATABSE VIEWS.

Re: JOINS

Former Member — Tue, 11 Sep 2007 10:02:32 GMT

use select statement with for ALL ENTRIES OPTION.

In the majority of cases inner joins will actually be the requirement, but in the minority of cases you will want to use an outer join. Outer joins are useful where you may need all records from a certain table, that meet the selection criteria, and all data from another table(s), IF it exists. But if the latter data does not exist, you still want to get the first table data.

Using outer joins places a heavy load on the database engine (although no heavier that if you coded a nested select), so be very parsimonious about the use of outer joins.

The performance of the join depends on the database optimizer used especially if there are more than two tables used for joins.

Try to give maximum number of conditions in the ON clause. This is because the ON conditions are evaluated first and the virtual table created as a result is the one on which the WHERE clause applies.

Use subqueries if possible.

Make sure you follow this :

Consolidated selections

No row by row processing

No check statements

No selections within loops

Selections are 'into' internal tables - no appends

SQL trace check completed

All programs checked to make sure that they are using the full index and in the

Correct order.

Minimum or zero number of identical selects.

Use of appropriate Indexes

check this code for your problem .

A real life scenario that has been optimized.

Data : Begin of I_vbak occurs 0,

Vbeln like vbak-vbeln,

End of I_vbak.

Data : Begin I_vbap occurs 0,

Vbeln like vbap-vbeln,

Posnr like vbap-posnr,

Matnr like vbap-matnr,

Kwmeng like vbap-kwmeng,

End of I_vbap.

Data : Begin of I_makt occurs 0,

Matnr like makt-matnr,

Maktx like makt-maktx,

End of I_makt.

Data : d_lines like sy-subrc.

Start-of-selection.

Refresh : I_vbak , I_vbap , I_makt.

Select vbeln from VBAK into table I_vbak

Where erdat = p_erdat And

vkorg = p_vkorg And

vtweg = p_vtweg And

spart = p_spart.

Clear d_lines.

Describe table I_vbak lines d_lines.

Check d_lines <> 0.

Select vbeln posnr matnr kwmeng from VBAP into table I_vbap

For all entries in table I_vbak

Where vbeln = I_vbak-vbeln.

Clear d_lines.

Describe table I_vbap lines d_lines.

Check d_lines <> 0.

End-of-selection.

Sort I_vbap by vbeln matnr.

Select matnr maktx from makt into I_makt

For all entries in I_vbap

Where spras = sy-langu And

matnr = I_vbap-matnr.

Sort I_makt by matnr.

Loop at I_vbap.

Clear I_makt.

Read I_makt with key matnr = I_vbap-matnr binary search.

Write 😕 I_vbnap-vbeln,

I_vbap-psonr,

I_vbap-matnr,

I_Makt-matnr,

I_vbap-kwmeng.

Endloop.

Reward points if helpful .

Revert back for more help.

Regards

vijay

Re: JOINS

Former Member — Tue, 11 Sep 2007 10:02:51 GMT

http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm

Re: JOINS

former_member189629 — Tue, 11 Sep 2007 10:03:08 GMT

Check these threads out...

Reward if helpful,

Karthik

Re: JOINS

messier31 — Tue, 11 Sep 2007 10:08:07 GMT

Hi,

Inner Join:-

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

Left Outer Join

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 = 'FRANKFURT'.

Enjoy SAP.

Pankaj Singh.

Re: JOINS

Former Member — Tue, 11 Sep 2007 10:08:14 GMT

Hi,

Inner join:

REPORT demo_select_inner_join.

DATA: BEGIN OF wa,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

fldate TYPE sflight-fldate,

bookid TYPE sbook-bookid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY carrid connid fldate bookid.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

LOOP AT itab INTO wa.

AT NEW fldate.

WRITE: / wa-carrid, wa-connid, wa-fldate.

ENDAT.

WRITE / wa-bookid.

ENDLOOP.

This example links the columns CARRID, CONNID, FLDATE, and BOOKID of the table SPFLI, SFLIGHT, and SBOOK, and creates a list of booking numbers for all flights from Frankfurt to New York that are not fully booked. An alias name is assigned to each table.

Left outer join:

REPORT demo_select_left_outer_join.

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 = 'FRANKFURT'.

LOOP AT itab INTO wa.

WRITE: / wa-carrid, wa-carrname, wa-connid.

ENDLOOP.

3 tables inner join:

Select single Vbrk_{Bukrs Vbrk}Kunrg Vbrk~Vbeln

Vbrk_{Fkdat Vbrk}Bstnk_Vf Vbrk~Zterm

Tvzbt~Vtext

Vbak_{Vbeln Vbak}Bstdk

Likp_{Vbeln Likp}lfdat Likp~Lfuhr

into w_vbrk

from vbrk

inner join Tvzbt on Tvzbt_{Zterm = Vbrk}Zterm and

Tvzbt~Spras = sy-langu

Inner join Vbfa as SalesLnk

on SalesLnk~vbeln = pu_vbeln and

SalesLnk~vbtyp_v = c_order

inner join Vbak on Vbak_{Vbeln = SalesLnk}Vbelv

Inner join Vbfa as DeliveryLnk

on DeliveryLnk~vbeln = pu_vbeln and

DeliveryLnk~vbtyp_v = c_Delivery

inner join Likp on Likp_{Vbeln = DeliveryLnk}Vbelv

where vbrk~vbeln = pu_Vbeln.

Regards,

Bhaskar