topic Re: problem in joins in Application Development and Automation Discussions

problem in joins

Former Member — Thu, 05 Jul 2007 11:32:03 GMT

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????????

Re: problem in joins

Former Member — Thu, 05 Jul 2007 14:11:56 GMT

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

Re: problem in joins

Former Member — Thu, 05 Jul 2007 16:04:51 GMT

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

Re: problem in joins

Former Member — Thu, 05 Jul 2007 17:36:27 GMT

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.

Re: problem in joins

Former Member — Fri, 06 Jul 2007 04:28:06 GMT

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