inner join between same table

[Former Member]

I want to implement a inner join on a internal table for eg this is my table structure

ID	       PRE	               SUC
L000000001	   1	               N000000001
  L000000002	   1	               N000000002
 L000000004	N000000001	N000000003
L000000005	N000000001	N000000005
L000000006	N000000001	N000000006
L000000012	N000000002	N000000012
L000000009	N000000002	N000000009
L000000003	N000000003	N000000004
L000000008	N000000005	N000000008
L000000007	N000000006	N000000007
L000000010	N000000009	N000000010
L000000011	N000000009	N000000011
L000000013	N000000012	N000000013

I want the inner join based on pre column of the table.

starting with the first value pre = 1 look for ir corresponding suc value n00000001 then pre = n0000000001 ans suc = n00000003, then pre = n00000003 suc = n00000004 etc

This chain continues for each and every value in the pre column. and would result in the internal table structure given below. I am confused how to achieve this. Can a inner join helps or if there are any other way in which I can do this. please suggest

PRE	               
                     1	                
            N000000001           
            N000000003	
            N000000004	
            N000000001	
	    N000000005	
	    N000000008	
	    N000000001	
	    N000000006	
	    N000000007	
                    1	       
	    N000000002	
            N000000012	
            N000000013
            N000000002
            N000000009
            N000000010
            N000000011

[Former Member]

if its tranparent table:

SELECT c~pre

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ztab AS c

INNER JOIN ztab AS b ON b_{sub = c}pre ).

[Former Member]

hi.

i think no inner join is possible on internal table.

first u should the above ID PRE SUC values in itab1.

now do like this.

loop at itab1 into wa_itab1.
 at new pre.
   wa_itab2-pre = wa_itab1-pre.
   wa_itab2-suc = wa_itab1-suc.
append wa_itab2 to itab2.
endat. " Itab2 is just like itab1 and wa_itab2 is just like wa_itab1.

[Former Member]

Hi,

You cannot use joins for internal table.

Use the following Codes:

data:t_pre type PRE.

Loop at <itab>.

If t_pre ne itab-pre.

write:pre,suc.

else.

write:suc.

endif.

t_pre = itab-pre.

Endloop.

Joins are only for DB tables.

Regards,

Gurpreet

[Former Member]

Hi priya,

Check the link below,

[http://help.sap.com/abapdocu/en/ABAPSELECT_JOIN.htm]

Hope it help u...

[Former Member]

I think you need to use recursive functionality for your requirement.

Try this program:

*&---------------------------------------------------------------------*
*& Report  ZTEST138
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

report  ZTEST138 no standard page heading line-count 21.

types: begin of T_ITAB,
  ID(10) type C,
  PRE(10) type C,
  SUC(10) type C,
  end of T_ITAB.

types: begin of T_PRE,
         PRE(10) type C,
       end of T_PRE.

data: I_ITAB type standard table of T_ITAB,
      I_PRE type standard table of T_PRE.

perform F_INITIATE.

perform F_RECURSIVE using '1'.

perform F_DISPLAY.

*&---------------------------------------------------------------------*
*&      Form  F_RECURSIVE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_0282   text
*----------------------------------------------------------------------*
form F_RECURSIVE  using  P_PRE type T_PRE-PRE.

  data: WA_ITAB type T_ITAB,
        WA_PRE type T_PRE.
  read table i_itab with key pre = p_pre
    transporting no fields.
  if sy-subrc eq 0.
    loop at I_ITAB into WA_ITAB where PRE = P_PRE.
      WA_PRE-PRE = WA_ITAB-pre.
      append WA_PRE to I_PRE.
      perform F_RECURSIVE using WA_ITAB-SUC.
    endloop.
  else.
    wa_pre-pre = p_pre.
    append wa_pre to i_pre.
  endif.

endform.                    " F_RECURSIVE

*&---------------------------------------------------------------------*
*&      Form  F_DISPLAY
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form F_DISPLAY .

  data: WA_PRE type T_PRE.
  loop at I_PRE into WA_PRE.
    write: / WA_PRE-PRE.
  endloop.
endform.                    " F_DISPLAY
*&---------------------------------------------------------------------*
*&      Form  F_INITIATE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form F_INITIATE .

  data: WA_ITAB type T_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000001'.
  WA_ITAB-PRE = '1'.
  WA_ITAB-SUC = 'N000000001'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000002'.
  WA_ITAB-PRE = '1'.
  WA_ITAB-SUC = 'N000000002'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000004'.
  WA_ITAB-PRE = 'N000000001'.
  WA_ITAB-SUC = 'N000000003'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000005'.
  WA_ITAB-PRE = 'N000000001'.
  WA_ITAB-SUC = 'N000000005'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000006'.
  WA_ITAB-PRE = 'N000000001'.
  WA_ITAB-SUC = 'N000000006'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000012'.
  WA_ITAB-PRE = 'N000000002'.
  WA_ITAB-SUC = 'N000000012'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000009'.
  WA_ITAB-PRE = 'N000000002'.
  WA_ITAB-SUC = 'N000000009'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000003'.
  WA_ITAB-PRE = 'N000000003'.
  WA_ITAB-SUC = 'N000000004'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000008'.
  WA_ITAB-PRE = 'N000000005'.
  WA_ITAB-SUC = 'N000000008'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000007'.
  WA_ITAB-PRE = 'N000000006'.
  WA_ITAB-SUC = 'N000000007'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000010'.
  WA_ITAB-PRE = 'N000000009'.
  WA_ITAB-SUC = 'N000000010'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000011'.
  WA_ITAB-PRE = 'N000000009'.
  WA_ITAB-SUC = 'N000000011'.
  append WA_ITAB to I_ITAB.

  clear WA_ITAB.
  WA_ITAB-ID = 'L000000013'.
  WA_ITAB-PRE = 'N000000012'.
  WA_ITAB-SUC = 'N000000013'.
  append WA_ITAB to I_ITAB.
endform.                    " F_INITIATE

[Former Member]

Hi Priya,,

Please check this code, this will resolve your issue for sure....

data : w_lines type i.
data : fs_itab1 like fs_itab,
suc like fs_itab1-suc.

describe table t_itab lines w_lines
loop at t_itab into fs_itab.
  write fs_itab-pre.
  suc = fs_itab1-suc.
  loop at itab into fs_itab1 where pre = suc.
  fs_itab = fs_itab1.
  while sy-subrc = 0.
     read table t_itab into fs_itab1 with key pre = fs_itab1-suc.
     if sy-subrc  = 0.
        if w_lines <> sy-tabix.
           w_lines = sy-tabix.
           write fs_itab1-pre.
           fs_itab = fs_itab1.
        endif.
     else.
        write fs_itab-suc.
     endif.
  endwhile.
  endloop.

Hope this helps you in a lot better way..

If there is any problem please do let me know what is the output.... you are getting on the list based on it we can modify the code little bit...

Regards,

Siddarth

[Former Member]

Hi Priya,

Try this....it gets the required output....

TYPES:
 BEGIN OF type_s_t1,
 id(10) TYPE c,
 pre(10) TYPE c,
 suc(10) TYPE c,
 END OF type_s_t1.
DATA: w_pre(10) TYPE c.
DATA: fs TYPE type_s_t1.
DATA itab1 LIKE TABLE OF fs.
DATA : w_tabix LIKE sy-tabix.
DATA :w_lin TYPE i.
DATA suc LIKE fs-suc.
DATA fs1 LIKE fs.
DATA itab2 TYPE string OCCURS 10 WITH HEADER LINE.

DEFINE m_data.
  clear fs.
  fs-id = &1.
  fs-pre = &2.
  fs-suc = &3.
  append fs to itab1.
END-OF-DEFINITION.


m_data 'L000000001'	'1'	       'N000000001'.
m_data 'L000000002'	'1'	       'N000000002'.
m_data 'L000000004'	'N000000001'	'N000000003'.
m_data 'L000000005'	'N000000001'	'N000000005'.
m_data 'L000000006'	'N000000001'	'N000000006'.
m_data 'L000000012'	'N000000002'	'N000000012'.
m_data 'L000000009'	'N000000002'	'N000000009'.
m_data 'L000000003'	'N000000003'	'N000000004'.
m_data 'L000000008'	'N000000005'	'N000000008'.
m_data 'L000000007'	'N000000006'	'N000000007'.
m_data 'L000000010'	'N000000009'	'N000000010'.
m_data 'L000000011'	'N000000009'	'N000000011'.
m_data 'L000000013'	'N000000012'	'N000000013'.

DESCRIBE TABLE itab1 LINES w_lin.

SET BLANK LINES ON.
LOOP AT itab1 INTO fs.
  APPEND INITIAL LINE TO itab2.
  w_pre = fs-pre.
  itab2 = w_pre.
  APPEND itab2.
  suc = fs-suc.
  fs1 = fs.
  LOOP AT itab1 INTO fs1 WHERE pre = suc.
    w_pre = fs1-pre.
    itab2 = w_pre.
    APPEND itab2.
    w_pre = fs1-suc.
    w_tabix = sy-tabix.
    DO.
      READ TABLE itab1 INTO fs WITH KEY pre = w_pre.
      IF sy-subrc = 0.
        IF w_tabix = sy-tabix.
          EXIT.
        ENDIF.
        itab2 = fs-pre.
        APPEND itab2.
        w_pre = fs-suc.
        w_tabix = sy-tabix.
      ELSE.
        itab2 = w_pre.
        APPEND itab2.
        EXIT.
      ENDIF.
    ENDDO.
  ENDLOOP.
  IF fs = fs1.
    itab2 = fs1-suc.
    APPEND itab2.
  ENDIF.
ENDLOOP.

LOOP AT itab2.
  WRITE:/ itab2.
ENDLOOP.

Regards,

Mdi.Deeba