problem in joins

[Former Member]

hi all,

i had a program which i couldnt get the results.so i divided this program and made the first two tables seperately and the rest of ther tables seperately as different programs. then iam able to get the results, but when i gave everything in one program im not getting .

my seperate codes :

first two tables are joined as follows:

SELECT T_{MNGRP S}BAUTL INTO CORRESPONDING FIELDS OF TABLE ITAB1

FROM QMSM AS T LEFT OUTER JOIN QMFE AS S ON S_{QMNUM = T}QMNUM

WHERE ( T~MNCOD EQ '3050'

AND T~MNCOD NE '3100' )

AND T~MNGRP IN CODGROUP.

CALL SCREEN 0001.

rest of the tables joined as follows :

SELECT A_{MAKTX B}VBELN B_{POSNR C}EBELN C_{EBELP C}MENGE C_{WEMNG C}EINDT

INTO CORRESPONDING FIELDS OF TABLE STAB FROM

( MAKT AS A INNER JOIN VBAP AS B ON A_{MATNR = B}MATNR

INNER JOIN EKPO AS D ON A_{MATNR = D}MATNR

INNER JOIN EKET AS C ON D_{EBELN = C}EBELN )

WHERE B~WERKS IN PLANT

AND B~MATNR IN MATERIAL

AND A~SPRAS = 'D'.

CALL SCREEN 0001.

the above two codes were given in two different programs. then i got the result

but when i gave together as follows:

SELECT

A~OTGRP

A~BAUTL

B~MAKTX

C~VBELN

C~POSNR

E~EBELN

E~EBELP

E~MENGE

E~WEMNG

E~EINDT

INTO CORRESPONDING FIELDS OF TABLE STAB FROM

( QMSM AS F INNER JOIN QMFE AS A ON A_{QMNUM = F}QMNUM

LEFT OUTER JOIN MAKT AS B ON A_{MATNR = B}MATNR

INNER JOIN VBAP AS C ON B_{MATNR = C}MATNR

INNER JOIN EKPO AS D ON B_{MATNR = D}MATNR

INNER JOIN EKET AS E ON D_{EBELN = E}EBELN )

WHERE C~WERKS IN PLANT

AND C~MATNR IN MATERIAL

• AND B~SPRAS = 'D'

AND A~OTGRP IN CODGRUP

AND F~MNCOD = '3050'.

CALL SCREEN 0001.

so could anybody help me in this aspect????????

[Former Member]

Problem could data may not be there for the conditions you entered in the query.

Please check with this:

SELECT A~OTGRP

A~BAUTL

B~MAKTX

C~VBELN

C~POSNR

E~EBELN

E~EBELP

E~MENGE

E~WEMNG

E~EINDT

INTO CORRESPONDING FIELDS OF TABLE STAB FROM

( QMSM AS F INNER JOIN QMFE AS A

ON A_{QMNUM = F}QMNUM

LEFT OUTER JOIN MAKT AS B

ON A_{MATNR = B}MATNR

INNER JOIN VBAP AS C

ON B_{MATNR = C}MATNR

INNER JOIN EKPO AS D

ON A_{MATNR = D}MATNR "<----

---

Replace B_{MATNR with A}MATNR

INNER JOIN EKET AS E

ON D_{EBELN = E}EBELN )

WHERE C~WERKS IN PLANT

AND C~MATNR IN MATERIAL

• AND B~SPRAS = 'D'

AND A~OTGRP IN CODGRUP

AND F~MNCOD = '3050'.

Just a note: Avoid using too many joins this will affect performance.

Regards,

A.Singh

[Former Member]

Hi

In the first you used LEFT OUTER Join between QMSM and QMFE

but here you are using INNER JOIN change this

and use INNER JOIN with MAKT

SELECT

A~OTGRP

A~BAUTL

B~MAKTX

C~VBELN

C~POSNR

E~EBELN

E~EBELP

E~MENGE

E~WEMNG

E~EINDT

INTO CORRESPONDING FIELDS OF TABLE STAB FROM

( QMSM AS F <b>LEFT OUTER</b> JOIN QMFE AS A

ON A_{QMNUM = F}QMNUM

<b>INNER</b> JOIN MAKT AS B ON A_{MATNR = B}MATNR

INNER JOIN VBAP AS C ON B_{MATNR = C}MATNR

INNER JOIN EKPO AS D ON B_{MATNR = D}MATNR

INNER JOIN EKET AS E ON D_{EBELN = E}EBELN )

WHERE C~WERKS IN PLANT

AND C~MATNR IN MATERIAL

• AND B~SPRAS = 'D'

AND A~OTGRP IN CODGRUP

AND F~MNCOD = '3050'.

CALL SCREEN 0001.

Hi but in real time we never join MAKT, VBAP and EKPO with just MATNR field.

Reward points for useful Answers

Regards

Anji

[Former Member]

Hi,

Table MAKT is not necessary, the material text is stored in both VBAP (ARKTX) and EKPO(TXZ01).

I agree with the others that it is a VERY bad idea to join a Sales Order to a Purchase Order via the material number. Could you please provide more infomation about the business requirement? There is special case in SD of a 'Third Party Order' where there is a direct link between PO & SO, but otherwise not.

[Former Member]

Hi,

Try to use either inner or outer joins for all tables as it contradicts the dependency of fields with each other. Try use ABAP query first to see if the joins work and then go for multiple joins this will be simple to check the dependency of fields.

I hope this works.

Pls do reward points for my effort.

Regards,

Ameet