topic Re: give syntax of inner join. with example program in Application Development and Automation Discussions

give syntax of inner join. with example program

Former Member — Thu, 16 Aug 2007 09:35:42 GMT

give syntax of inner join. with example program

Re: give syntax of inner join. with example program

Former Member — Thu, 16 Aug 2007 09:37:38 GMT

find the syntax in TCODE ABAPDOCU.

S@meer

Re: give syntax of inner join. with example program

Former Member — Thu, 16 Aug 2007 09:39:14 GMT

hi,

SYNTAX:

Select tab1_{field1 tab2}field2

into table t_table

from tab1

inner join tab2

on tab1_{field1 = tab2}field2

where field1 in s_field1.

copy paste the below program..

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.

rewards if useful,

regards,

'Nazeer'

Re: give syntax of inner join. with example program

Former Member — Thu, 16 Aug 2007 09:39:24 GMT

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

Select a_{airln a}lnnam b_{fligh b}cntry into table int_airdet

From zairln as a inner join zflight as b on a_{airln = b}airln.

In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

say lets have 2 tables VBAP and VBAK. they are as follows.

content of VBAK.

VBELN ERDAT ERNAM AUART

1001 20021011 10 12

1002 20021012 12 12

1003 20021011 13 14

Content of VBAP.

VBELN POSNR MATNR MATKL

1001 10 12 12

1002 13 12 14

1002 10 1 1

Now we have VBELN as the Key for these two tables. so we write the select query as

SELECT vbak~vbeln

vbak~erdat

vbak~ernam

vbak~auart

vbap~posnr

vbap~matnr

vbap~matkl

INTO TABLE tbl_values

FROM vbak JOIN vbap ON vbap_{vbeln = vbak}vbeln.

The output will be

VBELN ERDAT ERNAM AUART POSNR MATNR MATKL

1001 20021011 10 12 10 12 12

1002 20021012 12 12 13 12 14

1002 20021012 12 12 10 1 1

regards,

srinivas

<b>*reward for useful answers*</b>

Re: give syntax of inner join. with example program

Former Member — Thu, 16 Aug 2007 09:40:13 GMT

select mara_{matnr makt}maktx from mara inner join makt on mara-matnr eq makt-matnr.

reward points to all helpful answers

kiran.M

Re: give syntax of inner join. with example program

varma_narayana — Thu, 16 Aug 2007 09:40:48 GMT

Hi..

&----

*& Report YRP00_18 *

*& *

&----

REPORT YRP00_18 .

TYPES: BEGIN OF FLIGHT,

CARRID TYPE SCARR-CARRID,

CARRNAME TYPE SCARR-CARRNAME,

CONNID TYPE SPFLI-CONNID,

CITYFROM TYPE SPFLI-CITYFROM,

CITYTO TYPE SPFLI-CITYTO,

END OF FLIGHT.

DATA : IT_FL TYPE TABLE OF FLIGHT ,

WA_fL TYPE FLIGHT.

SELECT SCARR_{CARRID SCARR}CARRNAME

SPFLI_{CONNID SPFLI}CITYFROM SPFLI~CITYTO

FROM SCARR AS SCARR

INNER JOIN SPFLI AS SPFLI

ON SCARR_{CARRID = SPFLI}CARRID

INTO TABLE IT_FL.

LOOP AT IT_FL INTO WA_FL.

WRITE:/ WA_FL-CARRID,

WA_FL-CARRNAME,

WA_FL-CONNID,

WA_FL-CITYFROM,

WA_FL-CITYTO.

ENDLOOP.

<b>Reward if helpful</b>