topic Join condition in Application Development and Automation Discussions

Join condition

Former Member — Fri, 18 Jan 2008 04:40:46 GMT

Dear all,

I am new in ABAP.

Now i got one report in which 5 tables are there.Can you suggest me that Can I use inner join.OR is there any other method.please tell me with example.

Thanks in advance.

Re: Join condition

Former Member — Fri, 18 Jan 2008 04:45:10 GMT

rather than using inner joins use select for all entries iit increases performance

for example and syntax search in formus

Re: Join condition

former_member156446 — Fri, 18 Jan 2008 04:45:39 GMT

Joining 5 tables is not preferred.. performance point of view..

IF NOT lt_vbak[] IS INITIAL.

SELECT vbeln vposn vbegreg vendreg vabndat vuntdat vlaufz vlauez vbegdat

venddat vlaufk FROM veda

INTO TABLE lt_veda

FOR ALL ENTRIES IN lt_vbak

WHERE vbeln EQ lt_vbak-vbeln.

SELECT a_{vbeln b}posnr a_{auart a}vkorg a_{vtweg a}spart a~ktext

a_{guebg a}gueen a_{faksk a}audat a_{waerk a}abrvw b_{mvgr4 b}matnr

a~knumv

INTO TABLE lt_vbak " all header items

FROM vbak AS a

JOIN vbap AS b

ON a_{vbeln = b}vbeln

WHERE a~vbeln IN so_vbeln

AND a~erdat IN so_cdat

AND a~auart IN ('ZCMV', 'ZCSE', 'ZCSS')

AND a~vkorg IN so_vkorg.

Award points if helpful

Re: Join condition

Former Member — Fri, 18 Jan 2008 04:46:02 GMT

Hi,

Inner join: If you have common fields between 2 or more tables, its betterto use Inner join.

Check the below example:

VBAK & VBAP table has common fields, hence we can use inner join.

SELECT a_{kunnr a}vbeln a_{netwr a}bnddt a~knumv

a_{vkbur a}erdat a_{vdatu a}augru

a_{ktext b}matnr b_{arktx b}kwmeng b~kzwi6

b_{volum b}posnr b~kdmat

INTO CORRESPONDING FIELDS OF TABLE g_t_quot

FROM vbak AS a INNER JOIN vbap AS b

ON a_{vbeln = b}vbeln

WHERE a~vbeln IN so_vbeln

AND a~trvog ='2'

AND a~vkorg IN so_vkorg

AND a~vtweg IN so_vtweg

AND a~vkbur IN so_vkbur

AND a~audat IN so_audat

AND a~kunnr IN so_kunag.

FOR ALL ENTRIES:

If you get some data into one internal table and if you want to fetch data from other table based on it, use FOR ALL ENTRIES.

g_t_quot is an internal table.

SELECT spras augru bezei FROM tvaut INTO TABLE g_t_tvaut

FOR ALL ENTRIES IN g_t_quot

WHERE augru = g_t_quot-augru AND spras = sy-langu.

Re: Join condition

Former Member — Fri, 18 Jan 2008 04:59:35 GMT

select t1_{field1 t1}field2 t2_{field1 t2}field2 t3_{field1 t3}field2 t4_{field1 t4}field2 t5_{field1 t5}field2 into itab from (((( tabname1 as t1 inner join tabname2 as t2 on t1_{field = t2}field) inner join tabname3 as t3 on t2_{field = t3}field) inner join tabname4 as t4 on t3_{field = t4}field) inner join tabname5 as t5 on t4_{field = t5}field)

Re: Join condition

Former Member — Fri, 18 Jan 2008 05:15:49 GMT

Hi amar,

Don't use inner join for 5 tables because if use then ur program performance will decrease.So better use For All Entries.Then it will join not only 5 tables more than 5 also.For example below program is mine.Just check it once.

SELECT dokar

doknr

dokvr

dokst

zdm_doc_owner_id

FROM draw

INTO CORRESPONDING FIELDS OF TABLE it_doc_details

WHERE dokst = 'RL'

AND dokar IN (c_00,c_12,c_68,c_70,c_frm,c_pol,c_cs,c_qa). " retrieve the data from Draw table.

IF sy-subrc = 0.

Retrieving the release dates from the Drap table.*********************************************

SELECT dokar

doknr

dokvr

stzae

datum

FROM drap

INTO CORRESPONDING FIELDS OF TABLE it_drap

FOR ALL ENTRIES IN it_doc_details

WHERE dokar = it_doc_details-dokar

AND doknr = it_doc_details-doknr

AND dokvr = it_doc_details-dokvr. " retrieve the data from Drap table and join to it_doc_details.

IF sy-subrc = 0.

SORT it_drap BY dokar doknr dokvr stzae DESCENDING .

DELETE ADJACENT DUPLICATES FROM it_drap COMPARING dokar doknr dokvr .

Retrieving the documents description from the Drat table.*********************************************

SELECT dokar

doknr

dokvr

dktxt

FROM drat INTO CORRESPONDING FIELDS OF TABLE it_drat

FOR ALL ENTRIES IN it_doc_details

WHERE dokar = it_doc_details-dokar

AND doknr = it_doc_details-doknr

AND dokvr = it_doc_details-dokvr. " retrieve the data from Drat table and join to it_doc_details.

Here i showed only 3 tables. In the same fashion you can use 5 tables also.

if it is ok Reward me

Re: Join condition

Former Member — Fri, 18 Jan 2008 08:38:36 GMT

hi amar,

you can use joins. for all entries is the second alternative for joins.

because when you use joins you write single select query to get the data from multiple tables, so you hit the database server one time only. where in case of for all entries, you will write multiple select queries depending on the no.of tables you want to fetch the data.

so joins results less trafic when compared to for all entries