topic problem with inner join!!!! in Application Development and Automation Discussions

problem with inner join!!!!

Former Member — Mon, 02 Jul 2007 09:08:57 GMT

i used inner join for joining 4 tables in my program. but i m not sure that the select statement is correct. but its showing syntactically correct in my program.

the problem is im not getting the output.

could any body help me in my code

REPORT Z_DELIVERY_DETAILS.

TABLES : QMFE, QMSM, MAKT, VBAP, EKPO, EKET.

SELECT-OPTIONS : PLANT FOR VBAP-WERKS,

CODGRUP FOR QMFE-OTGRP,

MATERIAL FOR MAKT-MATNR.

DATA : BEGIN OF ITAB OCCURS 0,

MNCOD LIKE QMSM-MNCOD,

OTGRP LIKE QMFE-OTGRP,

BAUGRUPPE LIKE QMFE-BAUTL,

MATEXT LIKE MAKT-MAKTX,

SALESORDR LIKE VBAP-VBELN,

SALESPOS LIKE VBAP-POSNR,

PURORDER LIKE EKET-EBELN,

PURPOS LIKE EKET-EBELP,

SHDQTY LIKE EKET-MENGE,

QTYREC LIKE EKET-WEMNG,

EINDT LIKE EKET-EINDT,

END OF ITAB.

DATA : STAB LIKE TABLE OF ITAB.

DATA : CONTAINER TYPE REF TO CL_GUI_CUSTOM_CONTAINER,

GRID TYPE REF TO CL_GUI_ALV_GRID,

gt_fieldcat TYPE lvc_t_fcat.

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 F_{QMNUM = A}QMNUM

INNER 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 A~OTGRP IN CODGRUP

AND F~MNCOD = '3050'.

CALL SCREEN 0001.

Re: problem with inner join!!!!

Former Member — Mon, 02 Jul 2007 09:10:53 GMT

Hi,

Try to debug the code and check, you will know whether data is coming or not.

Paste your code here.

Thanks,

Anitha

Re: problem with inner join!!!!

Former Member — Mon, 02 Jul 2007 09:13:58 GMT

Hi,

Refer these links it will give You the syntax for Inner joins,

http://help.sap.com/saphelp_nw2004s/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/frameset.htm.

Regards,

Sudha S.

Re: problem with inner join!!!!

Former Member — Mon, 02 Jul 2007 09:15:52 GMT

Hi,

hope there is no data present in db for Criteria u r giving.

Jogdand M B

Re: problem with inner join!!!!

Former Member — Mon, 02 Jul 2007 09:19:36 GMT

Hi abhilash venkata,

Try to debug your "select" by commenting a "join line" then test it.

perform a step by step debug like this.

Hope this helps,

Erwan

Re: problem with inner join!!!!

Former Member — Thu, 05 Jul 2007 11:29:55 GMT

hi erwan,

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