topic Re: inner join coding in Application Development and Automation Discussions

inner join coding

Former Member — Fri, 25 Jul 2008 00:35:06 GMT

Hi gurus,

Can anyone help me how to change the following select statement into inner join?

SELECT aufnr FROM afpo INTO TABLE i_afpo

WHERE projn = l_afpo-projn

AND aufnr IN

( select AUFNR from AUFK WHERE auart = 'NT01' ).

IF sy-subrc = 0.

LOOP AT i_afpo.

SELECT SINGLE aufpl INTO l_aufpl

FROM afko

WHERE aufnr = i_afpo-aufnr.

IF sy-subrc = 0.

SELECT objnr INTO TABLE i_afvc

FROM afvc

WHERE aufpl = l_aufpl.

\[removed by moderator\]

Thanks & Regards.

Edited by: Jan Stallkamp on Jul 25, 2008 11:00 AM

Re: inner join coding

nikhil_chitre — Fri, 25 Jul 2008 02:23:44 GMT

Hi,

SELECT afpo~aufnr

afko~aufpl

afvc~objnr

FROM afpo INNER JOIN afko ON afpo_{aufnr = afko}aufnr

INNER JOIN afvc ON afko_{aufpl = afvc}aufpl

INTO TABLE itab WHERE afpo~projn = (your select Condition )

and afpo~aufnr = (your select Condition)..

I would recommend you not using Inner join as this would give you bad performance isues...

Instead use For all Entries.

Regards,

Nikhil

Re: inner join coding

Former Member — Fri, 25 Jul 2008 03:28:42 GMT

Hi, maybe thi will help you.

SELECT AFPO_{AUFNR AFVC}OBJNR

FROM AFKO

JOIN AFPO

ON AFKO_{AUFNR = AFPO}AUFNR

JOIN AUFK

ON AFKO_{AUFNR = AUFK}AUFNR AND

AUFK~AUART = 'NT01'

JOIN AFVC

ON AFKO_{AUFPL = AFVC}AUFPL

INTO (INTERNAL TABLE)

WHERE AFPO_{PROJN = L_AFPO}PROJN.

LOOP AT (INTERNAL TABLE).

...

*filter internal table for your data needed.

ENDLOOP.

PS:please don't write select statement in loop.because it will effect the processing speed.

Re: inner join coding

Former Member — Fri, 25 Jul 2008 06:54:09 GMT

for inner join you need to find the primary keys for the tables you want to join then the join statments will work you can do 4 inner joins in one select statments which is recomended

EX:

SELECT VBRK~VBELN AS VBELN

VBRK~VKORG AS VKORG

VBRK~VTWEG AS VTWEG

VBRP~PRODH AS PRODH

VBRK~FKDAT AS FKDAT

SUM( VBRP~FKLMG ) AS FKLMG

SUM( VBRP~NETWR ) AS NETWR

INTO CORRESPONDING FIELDS OF WA_TAB FROM VBRK AS VBRK JOIN VBRP AS VBRP

ON VBRK_{VBELN = VBRP}VBELN

WHERE VBRP~MATNR = WA_TAB-MATNR

AND VBRP~WERKS = WA_TAB-WERKS

AND VBRK~FKDAT BETWEEN S_DATE-LOW AND S_DATE-HIGH

AND VBRK~VKORG IN S_VKORG

AND VBRK~VTWEG IN S_VTWEG

AND VBRK~FKSTO NE 'X'

AND VBRK~FKART EQ 'ZBI1'

AND ( VBRK_{VBTYP EQ'M' OR VBRK}VBTYP EQ 'P' OR VBRK_{VBTYP EQ 'O' OR VBRK}VBTYP EQ '6'

OR VBRK~VBTYP EQ '5' )

GROUP BY VBRK_{VBELN VBRK}VKORG VBRK_{VTWEG VBRP}PRODH VBRK_{FKDAT VBRP}FKLMG.

Re: inner join coding

Former Member — Fri, 25 Jul 2008 06:56:02 GMT

why u go for inner join ? its better to go for For all entries!

Regards

Sas

\[removed by moderator\]

Edited by: Jan Stallkamp on Jul 25, 2008 11:03 AM

Re: inner join coding

Former Member — Fri, 25 Jul 2008 07:52:48 GMT

hiii

it is better to use FOR ALL ENTRIES here..if you want to use it then just refer to following code..

SELECT matnr                         " Material Number
    FROM mara
    INTO TABLE i_mara
   WHERE matnr IN s_matnr.


  IF i_mara[] IS NOT INITIAL.
    SELECT matnr                       " Material Number
           werks                       " Plants
           prctr                       " Profit Center
      FROM marc
      INTO TABLE i_marc
       FOR ALL ENTRIES IN i_mara
     WHERE matnr = i_mara-matnr
       AND werks IN s_werks.

  ENDIF.                               " IF i_mara[] IS NOT INITIAL

  i_output = i_marc.

  IF i_marc[] IS NOT INITIAL.

    SELECT matnr                       " Material Number
           werks                       " Plants
           lgort                       " Storage Location
      FROM mard
      INTO TABLE i_mard
       FOR ALL ENTRIES IN i_marc
     WHERE matnr EQ i_marc-matnr
       AND werks EQ i_marc-werks
       AND lgort IN s_lgort.

  ENDIF.                               " IF i_mara[] IS NOT INITIAL

If it is needed to use inner join then just refer to following example in link..it will solve your problem

http://www.sap-img.com/abap/select-statement-with-inner-join-is-taking-forever.htm

regards

twinkal

Re: inner join coding

former_member787646 — Fri, 25 Jul 2008 07:59:58 GMT

Try the following and check.

SELECT afpo~aufnr INTO TABLE i_afpo FROM AFPO

INNER JOIN AUFK ON AFPO_{aufnr = AUFK}aufnr

WHERE AUFK~auart = 'NT01'.

Hope this would help you.

Murthy

Edited by: Kalyanam Seetha Rama Murthy on Jul 25, 2008 10:00 AM