inner joins

[Former Member]

Hi folks,

i have a requirement where i have to join 5 tables VAPMA,VBUP,VBAK,VBAP,VBKD and my selection parameters are

matnr , sold to party, ship to party, PO number , PO date , price group, sales order type item cat type. Could you please help me in writing out an inner join on these tables .

thanks

[Former Member]

Hi folks,

i have a requirement where i have to join 5 tables VAPMA,VBUP,VBAK,VBAP,VBKD and my selection parameters are

matnr , sold to party, ship to party, PO number , PO date , price group, sales order type item cat type. Could you please help me in writing out an inner join on these tables .

thanks

[Former Member]

Why don't you give this a try yourself and if you have problems, come back to the forum with the code you wrote?

Rob

[Former Member]

Hi,

It is NOT ADVISABLE to use join on transaction data tables. So what you can do is, select the data as below:

1) Select <b>FIEDS</b> from VBAK where KUNNR (Sold To Party), KNKLI (Ship To Party), AUART (Order Type) from Selection Screen.

2) Select <b>FIELDS</b> from VBAP for all entries in VBAK comparing VBELN and MATNR, PSTYV (Item Category, if the reference here is Sales Item Category) from Selection Screen.

3) Select <b>FIELDS</b> from VBKD for all entries in VBAP comparing VBELN and POSNR and PO Number and Date from Selection Screen

4) Remove the un necessary records from VBAP & VBAK based on VBELN and POSNR found from VBKD.

5) Select <b>FIELDS</b> from VBUP for all entries in VBAP comparing VBELN, POSNR

6) Select <b>FIELDS</b> from VAPMA for all entries in VBAP comparing VBELN, POSNR.

Now start processing by giving first loop on VBAK and then sebsequent loops inside based on VBELN and POSNR (for item level data).

Thats it..

Hope this solves your problem. If not, write it back.

Regards,

Sandip

[Former Member]

hi ,

i have wriiten a join could you please correct it if any errors.

SELECT a~MATNR

a~KUNNR

a~BSTNK

a~VBELN

a~POSNR

a~ADRNR

b~POSNR

b~RFSTA

b~RFGSA

b~LFSTA

b~LFGSA

c~AUDAT

c~AUART

c~BSTNK

c~ZZCANCEL_AFTER

d~PSTYV

d~NETWR

d~KWMENG

d~ISMTITLE

e~PARVW

e~KUNNR

e~ADRNR

f~VBELN

f~KONDA

f~ZTERM

f~BSTKD

f~BSTDK

f~BSARK

FROM VAPMA as a INTO TABLE IT_VAPMA

on MATNR IN S_MATNR

or KUNNR IN S_KUNNR

or BSTNK IN S_BSTNK

inner join vbup as b into table it_vbup

on a_{vbeln = b}vbeln

AND a_{POSNR = b}POSNR

AND b~RFSTA NE 'C'

AND b~RFGSA NE 'C'

AND b~LFSTA NE 'C'

AND b~LFGSA NE 'C'

AND b~ABSTA NE 'C'

inner join vbak as c into table it_vbak

on b_{VBELN = c}VBELN

AND c~AUART IN S_AUART

AND c~BSTNK IN S_BSTNK

inner join vbap as d into table it_vbap

on c_{vbeln = d}vbeln

inner join vbpa as e into table it_vbpa on

d_{VBELN = e}VBELN

AND KUNNR IN S_KUNNR1

AND PARVW EQ C_WE.

inner join vbkd as f into table it_vbkd on

c~VBELN = f-VBELN

AND KONDA IN S_KONDA.

[Former Member]

Hi

select all the fields into a single internal table ITAB and use it

SELECT a~MATNR

a~KUNNR

a~BSTNK

a~VBELN

a~POSNR

a~ADRNR

b~POSNR

b~RFSTA

b~RFGSA

b~LFSTA

b~LFGSA

c~AUDAT

c~AUART

c~BSTNK

c~ZZCANCEL_AFTER

d~PSTYV

d~NETWR

d~KWMENG

d~ISMTITLE

e~PARVW

e~KUNNR

e~ADRNR

f~VBELN

f~KONDA

f~ZTERM

f~BSTKD

f~BSTDK

f~BSARK

INTO TABLE ITAB

FROM VAPMA as a inner join vbup as b

on a_{vbeln = b}vbeln AND a_{POSNR = b}POSNR

inner join vbak as c on b_{VBELN = c}VBELN

inner join vbap as d on c_{vbeln = d}vbeln

inner join vbpa as e on d_{VBELN = e}VBELN

inner join vbkd as f on c~VBELN = f-VBELN

where a~MATNR IN S_MATNR and

a~KUNNR IN S_KUNNR and

a~BSTNK IN S_BSTNK

AND b~RFSTA NE 'C'

AND b~RFGSA NE 'C'

AND b~LFSTA NE 'C'

AND b~LFGSA NE 'C'

AND b~ABSTA NE 'C'

AND c~AUART IN S_AUART

AND c~BSTNK IN S_BSTNK

AND e~KUNNR IN S_KUNNR1

AND e~PARVW EQ C_WE

AND f~KONDA IN S_KONDA.

Reward points for useful Answers

Regards

Anji

[Former Member]

When you do a join, you put all selected fileds into one internal table. So instead of it_vbak and the others, you should have some other internal table (say IT_ALL) htat has all of the fields you will select.

Very good first attempt at it though.

Rob

Message was edited by:

Rob Burbank

[Former Member]

Hi Anji,

i am getting a short dump saying in a select access the read file could not be placed in the target field provided.

MATNR TYPE MATNR,

ISMTITLE TYPE ISMTITLE,

KWMENG TYPE KWMENG,

VBELN TYPE VBELN_VA,

KUNNR TYPE KUNNR,

NAME_FIRST TYPE BU_NAMEP_F,

NAME_LAST TYPE BU_NAMEP_L,

HOUSE_NUM1 TYPE AD_HSNM1,

STREET TYPE AD_STREET,

CITY2 TYPE AD_CITY2,

CITY1 TYPE AD_CITY1,

REGION TYPE REGIO,

POST_CODE1 TYPE AD_PSTCD1,

COUNTRY TYPE LAND1,

KUNNR1 TYPE KUNNR,

NAME_FIRST1 TYPE BU_NAMEP_F,

NAME_LAST1 TYPE BU_NAMEP_L,

HOUSE_NUM11 TYPE AD_HSNM1,

STREET1 TYPE AD_STREET,

CITY21 TYPE AD_CITY2,

CITY12 TYPE AD_CITY1,

REGION1 TYPE REGIO,

POST_CODE11 TYPE AD_PSTCD1,

COUNTRY1 TYPE LAND1,

KONDA TYPE KONDA,

BSTKD TYPE BSTKD,

BSTDK TYPE BSTDK,

AUDAT TYPE AUDAT,

• ZZCANCEL_AFTER TYPE ZSDB_CANCELAFTER,

NETWR TYPE NETWR_AP,

ZTERM TYPE DZTERM,

BSARK TYPE BSARK,

AUART TYPE AUART,

PSTYV TYPE PSTYV,

IDENTCODE TYPE ISMIDENTCODE,

ISMPUBLDATE TYPE ISMPUBLDATE,

ISMAVAILDATE TYPE MBDAT,

MATKL TYPE MATKL,

ISMARTIST TYPE ISMARTIST,

ADRNR TYPE ADRNR_AG,

ADRNR1 TYPE ADRNR_AG,

END OF TY_FINAL.

my final internal fields are as above.

Can you help me locating the problem.

thanks

[Former Member]

Rather than selecting into table ITAB, try selecting into corresponding fileds of table itab. This should at least get you past this dump.

Rob

[Former Member]

Hey,

I have none of the fields as mandatory in the selection screen would this be correct then if i wrote the inner join as above.. or i have to modify anything.

coz its getting 1000's of records.which is belive is incorrect.

any help wud be appreciated.

thanks