topic Re: Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Fri, 12 Sep 2008 06:37:40 GMT

Hi people,

I have a requirement where i have to fetch data from 2 table EKKO and EKPO for which the common field is EBELN. I want to use inner join for these tables. So, I have given EBELN field in the join condition. I also need to give a condition in the WHERE clause saying EBELN in EBELN value in the selection-screen parameter. Since we have 2 conditions given for the same field i.e., EBELN, it gives an error. How would I overcome this problem having in mind that both the conditions must be satisfied.

Re: Inner Join

Former Member — Fri, 12 Sep 2008 06:39:25 GMT

HI,

In the where clause you can give condition for twice.

i hope it may not throw you error.

i did id once.

check it once.

Re: Inner Join

Former Member — Fri, 12 Sep 2008 06:40:24 GMT

HI,

YOUR SYNTAX WILL BE

SELECT  (field list as EKKO~ OR EKPO~)
  INTO  CORRESPONDING FIELDS OF TABLE itab
  FROM  ( ( ekko
            INNER JOIN EKPO  ON ekko~VBELN = ekPo~VBELN    )
  WHERE FIELD NAME1 IN SELECTOPTION1
    AND FIELD NAME2 IN SELECTOPTION2

For detail concept , check

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

Regards,

Anirban

Re: Inner Join

Former Member — Fri, 12 Sep 2008 06:44:16 GMT

Hi,

Try this.

select a_{f1 b}f2

into table itab

from EKKO as a inner join EKPO as b

on a_{EBELN eq b}EBELN

where a~EBELN in EBELN.

Try your code by specifying a or b ebeln in the where condition.

Sharin.

Re: Inner Join

Former Member — Fri, 12 Sep 2008 06:49:44 GMT

Hi,

use table name in where clause.

below is sample code.

TABLES: ekko,ekpo.

PARAMETERS: p_ebeln like ekko-ebeln.

data: begin of itab occurs 0,

ebeln like ekko-ebeln,

ebelp like ekpo-ebelp,

end of itab.

select ekko_{ebeln ekpo}ebelp into TABLE itab

from ekko INNER JOIN ekpo on

ekko_{ebeln = ekpo}ebeln

where ekko~ebeln = p_ebeln.

loop at itab.

write:/ itab-ebeln,itab-ebelp.

endloop.

Re: Inner Join

Former Member — Fri, 12 Sep 2008 06:56:19 GMT

Mentioning EBELN both in join condition and in Where condition is giving an error.

Just take a look at what i have done:

SELECT a~ebeln

a~bsart

a~aedat

a~ekorg

a~ekgrp

a~inco1

a~ernam

a~knumv

a~exnum

b~ebelp

b~menge

b~txz01

FROM ekko AS a

INNER JOIN ekpo AS b

ON b_{ebeln EQ a}ebeln

INTO TABLE gt_ekko

WHERE ebeln IN s_ebeln

AND bsart IN s_bsart

AND aedat IN s_aedat

AND ekorg IN s_ekorg

AND ekgrp IN s_ekgrp

AND ernam IN s_ernam

AND zdat01 IN s_zdat01

AND loekz = SPACE

AND elikz = SPACE.

This gives an error saying EBELN has 2 conditions.

Re: Inner Join

Former Member — Fri, 12 Sep 2008 06:59:04 GMT

In the where condition you did not mention which ebeln should be considered. that is from which table it should take and compare.

Re: Inner Join

Former Member — Fri, 12 Sep 2008 07:13:29 GMT

Hi Jean,

SELECT a~ebeln

a~bsart

a~aedat

a~ekorg

a~ekgrp

a~inco1

a~ernam

a~knumv

a~exnum

b~ebelp

b~menge

b~txz01

FROM ekko AS a

INNER JOIN ekpo AS b

ON b_{ebeln EQ a}ebeln

INTO TABLE gt_ekko

WHERE a~ebeln IN s_ebeln

AND a~bsart IN s_bsart

AND a~aedat IN s_aedat

AND a~ekorg IN s_ekorg

AND a~ekgrp IN s_ekgrp

AND a~ernam IN s_ernam

AND zdat01 IN s_zdat01

AND loekz = SPACE

AND elikz = SPACE.

Similarly for other fields u hv to gove the table name....