topic Re: joins in Application Development and Automation Discussions

joins

Former Member — Fri, 15 Dec 2006 05:41:30 GMT

could you all send me some sample code in ineer join and outer join....

for learning purpose only.......

thanks & regards...

sam.....

Re: joins

Former Member — Fri, 15 Dec 2006 05:45:14 GMT

Hi Sam,

Chk this

http://www.sap-img.com/abap/inner-joins.htm

Re: joins

Former Member — Fri, 15 Dec 2006 05:46:24 GMT

Hi Sam

Please check this link:

Kind Regards

Eswar

Re: joins

Former Member — Fri, 15 Dec 2006 05:48:36 GMT

check this

http://www.sapdevelopment.co.uk/tables/tab_cooiselect.htm

-charitha

Re: joins

Former Member — Fri, 15 Dec 2006 05:49:50 GMT

hi,

<b>Specifying Two or More Database Tables as an Inner Join</b>

In a relational database, you normally need to read data simultaneously from more than one database table into an application program. You can read from more than one table in a single SELECT statement, such that the data in the tables all has to meet the same conditions, using the following join expression:

SELECT...

...

FROM <tab> [INNER] JOIN <dbtab> [AS <alias>] ON <cond> <options>

...

where <dbtab> is a single database table and <tab> is either a table or another join expression. The database tables can be specified statically or dynamically as described above. You may also use aliases. You can enclose each join expression in parentheses. The INNER addition is optional.

A join expression links each line of <tab> with the lines in <dbtab> that meet the condition <cond>. This means that there is always one or more lines from the right-hand table that is linked to each line from the left-hand table by the join. If <dbtab> does not contain any lines that meet the condition <cond>, the line from <tab> is not included in the selection.

The syntax of the <cond> condition is like that of the WHERE clause, although individual comparisons can only be linked using AND. Furthermore, each comparison must contain a column from the right-hand table <dbtab>. It does not matter on which side of the comparison it occurs. For the column names in the comparison, you can use the same names that occur in the SELECT clause, to differentiate columns from different database tables that have the same names.

The comparisons in the condition <cond> can appear in the WHERE clause instead of the ON clause, since both clauses are applied equally to the temporary table containing all of the lines resulting from the join. However, each join must contain at least one comparison in the condition <cond>.

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.

<b>Specifying Two or More Database Tables as a Left Outer Join</b>

In an inner join, a line from the left-hand database table or join is only included in the selection if there is one or more lines in the right-hand database table that meet the ON condition <cond>. The left outer join, on the other hand, reads lines from the left-hand database table or join even if there is no corresponding line in the right-hand table.

SELECT...

...

FROM <tab> LEFT [OUTER] JOIN <dbtab> [AS <alias>] ON <cond>

...

<tab> and <dbtab> are subject to the same rules and conditions as in an inner join. The OUTER addition is optional. The tables are linked in the same way as the inner join with the one exception that all lines selected from <tab> are included in the final selection. If <dbtab> does not contain any lines that meet the condition <cond>, the system includes a single line in the selection whose columns from <dbtab> are filled with null values.

In the left outer join, more restrictions apply to the condition <cond> than in the inner join. In addition to the above restrictions:

EQ or = is the only permitted relational operator.

There must be at least one comparison between columns from <tab> and <dbtab>.

The WHERE clause may not contain any comparisons with columns from <dbtab>. All comparisons using columns from <dbtab> must appear in the condition <cond>.

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

Regards,

Vara

Re: joins

Former Member — Fri, 15 Dec 2006 05:49:56 GMT

Hi Sam

Check in the SAP Documentation. You will find a lot of usefull stuff.

Check in transaction ABAPDOCU for sample code

Thanks

Elan

Re: joins

Former Member — Fri, 15 Dec 2006 05:50:10 GMT

Hi,

sample code for inner join is:

SELECT MKPF_{BUDAT MKPF}CPUTM

MSEG_{MATNR MSEG}WERKS MSEG~EBELN

MSEG_{EBELP MSEG}ERFMG

INTO CORRESPONDING FIELDS OF TABLE

W_DTL_INVOICE

FROM MKPF INNER JOIN MSEG

ON MKPF_{MBLNR = MSEG}MBLNR

AND MKPF_{MJAHR = MSEG}MJAHR

WHERE MKPF~BUDAT > '20040721' AND

MSEG~BWART = '101' AND

MSEG~BUKRS = '1733'.

for outer join : same code, replace the INNER JOIN with OUTER JOIN.

Please see the below link for more help:

<a href="http://help.sap.com/saphelp_erp2004/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm">http://help.sap.com/saphelp_erp2004/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm</a>

Thanks

Shiva

Re: joins

former_member842213 — Fri, 15 Dec 2006 06:05:59 GMT

Inner join:it ill work like intersection (it will take the common records based on the 'on condition').

Outer join : ill work as a union.

select fields

into table iternal table

from dbtabname innerjoin dbtabname1

on condition

where condition (depending upon the requirement)

select vbak_{vbeln vbak}ernam vbak_{erdat kna1}name1 into table itab

from vbak inner join kna1 on vbak_{kunnr = kna1}kunnr where vbak~erdat

ge date-low ."and vbak~erdat le date-high.

select mbew_{bwkey mara}matnr makt_{maktx marm}laeng marm_{hoehe marm}volum

marm_{voleh marm}brgew marm~gewei

into CORRESPONDING FIELDS OF table itab

from mbew

inner join mara on mbew_{matnr = mara}matnr

inner join makt on mara_{matnr = makt}matnr

inner join marm on makt_{matnr = marm}matnr

where mbew~bwkey = p_key and spras = 'EN'.