topic Re: Problem in Inner Join in Application Development and Automation Discussions

Problem in Inner Join

Former Member — Sat, 04 Sep 2010 02:49:06 GMT

SELECT A~FTRMI
              A~AUFNR
              B~MATNR
              D~MAKTX
              B~PSMNG
              B~WEMNG
              C~MTART
              E~CHARG
              C~MATKL
              C~SPART
              R~BWART
              R~CHARG
              R~MATNR
              E~BWART
              R~BDMNG
        INTO TABLE ITAB
        FROM AFKO AS A INNER JOIN AFPO AS B ON A~AUFNR = B~AUFNR
                       INNER JOIN MARA AS C ON B~MATNR = C~MATNR
                       INNER JOIN MAKT AS D ON C~MATNR = D~MATNR
                       INNER JOIN MSEG AS E ON B~MATNR = E~MATNR AND A~AUFNR = E~AUFNR AND B~DWERK = E~WERKS
                       INNER JOIN RESB AS R ON A~AUFNR = R~AUFNR AND E~AUFNR = R~AUFNR AND R~RSNUM = A~RSNUM
                                                                 AND R~WERKS = E~WERKS AND R~BAUGR = E~MATNR
                       INNER JOIN MARA AS C1 ON R~MATNR = C1~MATNR
        WHERE A~FTRMI IN S_DATE AND A~AUFNR IN S_AUFNR AND C~MTART IN S_TYPE AND C~MATKL = 'T'
        AND R~BWART ='261' AND E~BWART = '101'
        AND R~XWAOK ='X' AND B~DWERK = '2000'
        AND R~XLOEK EQ SPACE AND E~KZBEW ='F' AND D~SPRAS = 'E' AND R~KZEAR = 'X' AND C~MATNR IN S_MATNR.

This query is giving repetative records from RESB table. It combines each line of RESB with the other lines. Can anybody pls tell me is there any field remaining in join condition.

Pls Help. <<removed by moderator>>

Thanks & Regards

Swati Ghadge.

Edited by: kishan P on Sep 4, 2010 9:34 AM

Re: Problem in Inner Join

deepak_dhamat — Sat, 04 Sep 2010 04:51:19 GMT

SELECT A~FTRMI

A~AUFNR

B~MATNR

D~MAKTX

B~PSMNG

B~WEMNG

C~MTART

E~CHARG

C~MATKL

C~SPART

R~BWART

R~CHARG

R~MATNR

E~BWART

R~BDMNG

INTO TABLE ITAB

FROM AFKO AS A INNER JOIN AFPO AS B ON A_{AUFNR = B}AUFNR

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

INNER JOIN MAKT AS D ON C_{MATNR = D}MATNR

INNER JOIN MSEG AS E ON B_{MATNR = E}MATNR AND A_{AUFNR = E}AUFNR AND B_{DWERK = E}WERKS

INNER JOIN RESB AS R ON A_{AUFNR = R}AUFNR AND E_{AUFNR = R}AUFNR AND R_{RSNUM = A}RSNUM

AND R_{WERKS = E}WERKS AND R_{BAUGR = E}MATNR

INNER JOIN MARA AS C1 ON R_{MATNR = C1}MATNR

WHERE A_{FTRMI IN S_DATE AND A}AUFNR IN S_AUFNR AND C_{MTART IN S_TYPE AND C}MATKL = 'T'

AND R_{BWART ='261' AND E}BWART = '101'

AND R_{XWAOK ='X' AND B}DWERK = '2000'

AND R_{XLOEK EQ SPACE AND E}KZBEW ='F' AND D_{SPRAS = 'E' AND R}KZEAR = 'X' AND C~MATNR IN S_MATNR.

hi ,

Divide your inner join in two querys , in order to sagrigate data properly and understand problem easily .

you have used inner join on seven tables which may hamper performance of query .

Regards

Deepak.

Re: Problem in Inner Join

anup_deshmukh4 — Sat, 04 Sep 2010 05:09:13 GMT

Hello swatig ,

You can get a complex query generated by system itself and also check weather the query is fetching the data properly ,

carry on the following steps,

1. Go to sqvi Transaction

2. give a for the QUICK VIEW set and say create

3. Give title

4. Select data source as Table Join

5. Now you will come to a screen where you can add to TABLES ( hear you can actually connect..the table fields for join condition )

6. ( once you are done with the above process press back )

7. select the table fields you want for the list and the selection screen fields ( selection screen field will be those you want in your where condition of you join query )

8. Run the quick view give the proper selection screen fields see if you are getting your desired results if yes come to the selection screen check the program...this program will have invoked certain system generated Function module in that function module you will get your query..

its easy.... just try it once...!

Search more on same.... on Wiki or sdn

check for the below link you will y get your desired query...!

http://www.docstoc.com/docs/2585187/SAP-QUERY--SQ01-STEP-BY-STEP-GUIDE

Hope it Helps

Edited by: Anup Deshmukh on Sep 4, 2010 7:10 AM

Re: Problem in Inner Join

former_member217544 — Sat, 04 Sep 2010 06:54:54 GMT

Hi Swati,

Try using Distinct option with joins condition:


SELECT DISTINCT A~FTRMI
              A~AUFNR
              B~MATNR
              D~MAKTX
              B~PSMNG
              B~WEMNG
              C~MTART
              E~CHARG
              C~MATKL
              C~SPART
              R~BWART
              R~CHARG
              R~MATNR
              E~BWART
              R~BDMNG
        INTO TABLE ITAB
        FROM AFKO AS A INNER JOIN AFPO AS B ON A~AUFNR = B~AUFNR
.......

Thanks & Regards,

Swarna Munukoti.

Re: Problem in Inner Join

Clemenss — Sat, 04 Sep 2010 20:18:25 GMT

SELECT 
  AFKO~FTRMI
  AFKO~AUFNR            
  AFPO~MATNR            
  MAKT~MAKTX            
  AFPO~PSMNG            
  AFPO~WEMNG            
  MARA~MTART            
  MSEG~CHARG            
  MARA~MATKL            
  MARA~SPART            
  RESB~BWART            
  RESB~CHARG            
  RESB~MATNR            
  MSEG~BWART            
  RESB~BDMNG            
  INTO CORRESPONDING FIELDS OF TABLE ITAB "always use CORRESPONDING FIELDS!!!
  FROM AFKO
  INNER JOIN AFPO ON AFKO~AUFNR = AFPO~AUFNR
  INNER JOIN MARA ON AFPO~MATNR = MARA~MATNR
  INNER JOIN MAKT ON MARA~MATNR = MAKT~MATNR
  INNER JOIN MSEG ON AFPO~MATNR = MSEG~MATNR AND 
                     AFKO~AUFNR = MSEG~AUFNR AND 
                     AFPO~DWERK = MSEG~WERKS
  INNER JOIN RESB ON AFKO~AUFNR = RESB~AUFNR AND 
                     MSEG~AUFNR = RESB~AUFNR AND 
                     RESB~RSNUM = AFKO~RSNUM AND 
                     RESB~WERKS = MSEG~WERKS AND 
                     RESB~BAUGR = MSEG~MATNR
  INNER JOIN MARA AS C1 ON RESB~MATNR = C1~MATNR
  WHERE AFKO~FTRMI IN S_DATE 
    AND AFKO~AUFNR IN S_AUFNR 
    AND MARA~MTART IN S_TYPE 
    AND MARA~MATKL = 'T'
    AND RESB~BWART = '261' 
    AND MSEG~BWART = '101'
    AND RESB~XWAOK = 'X' 
    AND AFPO~DWERK = '2000'
    AND RESB~XLOEK EQ SPACE 
    AND MSEG~KZBEW = 'F' 
    AND MAKT~SPRAS = 'E' 
    AND RESB~KZEAR = 'X' 
    AND MARA~MATNR IN S_MATNR.

You need no AS alias except you join the same table twice, it helps only non-readability.

I think field AUFPS is missing in the join of MSEG and RESB, this may help. You should add MATNR as well because then the index RESB~M will be used.

Regards,

Clemens

Edited by: Clemens Li on Sep 4, 2010 10:20 PM

Re: Problem in Inner Join

Former Member — Sun, 05 Sep 2010 03:24:41 GMT

i don't think this will be the right way to get your date.

use for all entries to all of them then

loop

read

endloop.

you will also have better debugging to know data from each table youre getting

Re: Problem in Inner Join

Former Member — Sun, 05 Sep 2010 03:34:06 GMT

Hi every body,

Thanks for the help. will try each one.

Regards

Swati