topic Re: Inner Join Built? in Application Development and Automation Discussions

Inner Join Built?

Former Member — Mon, 21 May 2007 03:51:43 GMT

Hi Experts,

I need to build a inner join between 4 tbls, like, in such a way that, the user gives his choices in selection screen as SELECT OPTIONS / Range values,

1 - KUNNR-VBAK

2 - KVGR2 - VBAK

3 - BZIRK(dist) - KNVV-BZIRK

4- MATNR-VBAP

5 - ANGDT-VBAK - Valid from date

6- BNDDT-VBAK - Valid to date

7- BNAME-VBAK (name)

8 - PARNR-VBPA (AP type)

So, Pls- let me know,

1 - Which r higher level fields/tbls, I mean, In What order I hv to choose in WHERE clause?

2 - How to join second tbl to third tbl like, VBELN-VBPA and KNVV-BZIRK?

3 - It wuld b greate, if u provide the code!

4 - Guess, for SELECT clause, I can write in any order, I mean, need to worry the higher level filtering!

ThanQ.

Message was edited by:

Sridhar

Re: Inner Join Built?

Former Member — Mon, 21 May 2007 03:57:53 GMT

Hi,

Check this one.

TABLES: vbrk,vbrp,makt,kna1,t001.

SELECT-OPTIONS:

s_vkorg FOR vbrk-vkorg,

s_vbeln FOR vbrk-vbeln.

SELECT vbrk_{vbeln vbrk}fkdat vbrk_{zterm vbrk}inco1 vbrk~inco2

vbrk_{waerk vbrk}bukrs

vbrp_{matnr vbrp}fkimg vbrp_{vrkme vbrp}netwr

makt_{maktg kna1}name1 t001_{butxt kna1}adrnr

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( ( ( vbrk INNER JOIN vbrp ON vbrk_{vbeln = vbrp}vbeln )

INNER JOIN makt ON vbrp_{matnr = makt}matnr )

INNER JOIN kna1 ON vbrk_{kunag = kna1}kunnr )

INNER JOIN t001 ON vbrk_{bukrs = t001}bukrs )

WHERE vbrk~vkorg IN s_vkorg AND

vbrk~vbeln IN s_vbeln.

Thanks.

Re: Inner Join Built?

Former Member — Mon, 21 May 2007 04:50:36 GMT

Hi Sridhar,

Refer this code :

SELECT a~kunnr

a~KVGR2

a ~ANGDT

a~BNDDT

a~BNAME

b~MATNR

c~PARNR

d~BZIRK

INTO CORRESPONDING FIELDS OF TABLE itab

FROM VBAK as a inner join

VBAP as b on a_{vbeln = b}vbeln

VBFA as c on a_vbeln = c~vbeln

KNVV as d on a_{vkotrg = d}VKORG

WHERE ................................

Reward points if helpful.

Regards,

Hemant