foriegn key

[Former Member]

hi expert ,

i have created to custom tables .. ztable1 and ztable2 linked them up using foriegn key .. field roll

i m unable to fetch data from second table(ztable2) in my final table .. plz solve my query

&----

---

*& Report ZFORIEGN *

*& *

&----

---

*& *

*& *

&----

---

REPORT ZFORIEGN MESSAGE-ID ZMSG. .

tables: ztable1, ztable2.

data declaration********************

*********************************************

*internal tabble without headerline for vbak.

types: begin of ty_ztable1,

roll type zroll,

name type zname1,

class type zclass,

end of ty_ztable1.

data: it_ztable1 type standard table of ty_ztable1,

wa_ztable1 type ty_ztable1.

types: begin of ty_ztable2,

roll type zroll,

age type zage,

end of ty_ztable2.

data: it_ztable2 type standard table of ty_ztable2,

wa_ztable2 type ty_ztable2.

• *internal tabble without headerline for final.

types: begin of ty_final,

roll type zroll,

name type zname1,

class type zclass,

age type zage,

end of ty_final.

data: it_final type standard table of ty_final,

wa_final type ty_final.

• selection screen****************************

select-options: s_roll for ztable1-roll.

select event**********************

select roll name class from ztable1 into table it_ztable1

where roll in s_roll.

if sy-subrc <> 0.

• message e000.

leave list-processing.

else.

• if not it_ztable1[] is initial.

sort it_ztable1 by roll.

SELECT roll age

FROM ZTABLE2

INTO TABLE it_ZTABLE2

FOR ALL ENTRIES IN it_ZTABLE1

WHERE ROLL = ztable1-roll.

endif.

*

• IF sy-subrc <> 0.

*

*

• MESSAGE i005.

*

• LEAVE LIST-PROCESSING.

*

• ENDIF.

*ENDIF.

LOOP AT it_ztable1 INTO wa_ztable1.

wa_final-roll = wa_ztable1-roll .

wa_final-name = wa_ztable1-name .

wa_final-class = wa_ztable1-class .

APPEND wa_final TO it_final .

ENDLOOP .

*SORT it_ZTABLE2 BY ROLL.

loop at it_final into wa_final.

read table it_ztable2 into wa_ztable2

with key roll = wa_ztable1-roll binary search.

if sy-subrc <> 0.

wa_final-age = wa_ztable2-age.

• MODIFY it_final FROM wa_final INDEX sy-tabix ASSIGNING

*REFERENCE INTO dref tRANSPORTING posnr matnr.

MODIFY it_final FROM wa_final tRANSPORTING age.

clear wa_final.

*

ENDIF.

ENDLOOP.

WRITE:/1 sy-uline(63).

WRITE:/1'|', 2 'Roll No' COLOR 4 ON ,11'|',12 'Name' COLOR 5 ON

,21'|',22 'Class' COLOR 4 ON,35'|',

36 'Age' COLOR 5 ON.

WRITE:/1 sy-uline(63).

LOOP AT it_final INTO wa_final.

WRITE:/1'|', 2 wa_final-roll,11'|',12 wa_final-name,21'|',22

wa_final-class,35'|',

36 wa_final-age.

endloop.

Edited by: Matt on Nov 11, 2008 4:42 PM - moved to correct forum

[Former Member]

hi expert ,

i have created to custom tables .. ztable1 and ztable2 linked them up using foriegn key .. field roll

i m unable to fetch data from second table(ztable2) in my final table .. plz solve my query

&----

---

*& Report ZFORIEGN *

*& *

&----

---

*& *

*& *

&----

---

REPORT ZFORIEGN MESSAGE-ID ZMSG. .

tables: ztable1, ztable2.

data declaration********************

*********************************************

*internal tabble without headerline for vbak.

types: begin of ty_ztable1,

roll type zroll,

name type zname1,

class type zclass,

end of ty_ztable1.

data: it_ztable1 type standard table of ty_ztable1,

wa_ztable1 type ty_ztable1.

types: begin of ty_ztable2,

roll type zroll,

age type zage,

end of ty_ztable2.

data: it_ztable2 type standard table of ty_ztable2,

wa_ztable2 type ty_ztable2.

• *internal tabble without headerline for final.

types: begin of ty_final,

roll type zroll,

name type zname1,

class type zclass,

age type zage,

end of ty_final.

data: it_final type standard table of ty_final,

wa_final type ty_final.

• selection screen****************************

select-options: s_roll for ztable1-roll.

select event**********************

select roll name class from ztable1 into table it_ztable1

where roll in s_roll.

if sy-subrc <> 0.

• message e000.

leave list-processing.

else.

• if not it_ztable1[] is initial.

sort it_ztable1 by roll.

SELECT roll age

FROM ZTABLE2

INTO TABLE it_ZTABLE2

FOR ALL ENTRIES IN it_ZTABLE1

WHERE ROLL = ztable1-roll.

endif.

*

• IF sy-subrc <> 0.

*

*

• MESSAGE i005.

*

• LEAVE LIST-PROCESSING.

*

• ENDIF.

*ENDIF.

LOOP AT it_ztable1 INTO wa_ztable1.

wa_final-roll = wa_ztable1-roll .

wa_final-name = wa_ztable1-name .

wa_final-class = wa_ztable1-class .

APPEND wa_final TO it_final .

ENDLOOP .

*SORT it_ZTABLE2 BY ROLL.

loop at it_final into wa_final.

read table it_ztable2 into wa_ztable2

with key roll = wa_ztable1-roll binary search.

if sy-subrc <> 0.

wa_final-age = wa_ztable2-age.

• MODIFY it_final FROM wa_final INDEX sy-tabix ASSIGNING

*REFERENCE INTO dref tRANSPORTING posnr matnr.

MODIFY it_final FROM wa_final tRANSPORTING age.

clear wa_final.

*

ENDIF.

ENDLOOP.

WRITE:/1 sy-uline(63).

WRITE:/1'|', 2 'Roll No' COLOR 4 ON ,11'|',12 'Name' COLOR 5 ON

,21'|',22 'Class' COLOR 4 ON,35'|',

36 'Age' COLOR 5 ON.

WRITE:/1 sy-uline(63).

LOOP AT it_final INTO wa_final.

WRITE:/1'|', 2 wa_final-roll,11'|',12 wa_final-name,21'|',22

wa_final-class,35'|',

36 wa_final-age.

endloop.

Edited by: Matt on Nov 11, 2008 4:42 PM - moved to correct forum

[Former Member]

LOOP AT it_ztable1 INTO wa_ztable1.
wa_final-roll = wa_ztable1-roll .
wa_final-name = wa_ztable1-name .
wa_final-class = wa_ztable1-class .
APPEND wa_final TO it_final .


ENDLOOP .

*SORT it_ZTABLE2 BY ROLL.


loop at it_final into wa_final.

read table it_ztable2 into wa_ztable2
with key roll = wa_ztable1-roll binary search.  --> this need to be wa_final-roll
if sy-subrc 0.
wa_final-age = wa_ztable2-age.

    * MODIFY it_final FROM wa_final INDEX sy-tabix ASSIGNING

*REFERENCE INTO dref tRANSPORTING posnr matnr.

MODIFY it_final FROM wa_final tRANSPORTING age.

clear wa_final.

*
ENDIF.

ENDLOOP.

Instead of the above Code

LOOP AT it_ztable1 INTO wa_ztable1.
wa_final-roll = wa_ztable1-roll .
wa_final-name = wa_ztable1-name .
wa_final-class = wa_ztable1-class .

read table it_ztable2 into wa_ztable2
with key roll = wa_ztable1-roll binary search.  
If sy-subrc eq 0.
  wa_final-age = wa_ztable2-age.
endif.
APPEND wa_final TO it_final .
Clear : wa_ztable1, wa_ztable2, wa_final.
ENDLOOP .

[former_member189629]

Hi.

I did not understand why you added the following conditions:

select event**********************

select roll name class from ztable1 into table it_ztable1

where roll in s_roll.

if sy-subrc 0.

message e000.

leave list-processing.

else.

if not it_ztable1[] is initial.

sort it_ztable1 by roll.

SELECT roll age

FROM ZTABLE2

INTO TABLE it_ZTABLE2

FOR ALL ENTRIES IN it_ZTABLE1

WHERE ROLL = ztable1-roll.

endif.

Instead code like this:

select roll name class from ztable1 into table it_ztable1

where roll in s_roll.

if sy-subrc 0.

if not it_ztable1[] is initial.

sort it_ztable1 by roll.

SELECT roll age

FROM ZTABLE2

INTO TABLE it_ZTABLE2

FOR ALL ENTRIES IN it_ZTABLE1

WHERE ROLL = ztable1-roll.

endif.

else.

message eXXX.

endif.

[Former Member]

Hi,

First of all in your coding where you are checking sy-subrc = 0...

*****************************************************************

select roll name class from ztable1 into table it_ztable1

where roll in s_roll.

if sy-subrc 0.

message e000.

leave list-processing.

else.

if not it_ztable1[] is initial.

sort it_ztable1 by roll.

SELECT roll age

FROM ZTABLE2

INTO TABLE it_ZTABLE2

FOR ALL ENTRIES IN it_ZTABLE1

WHERE ROLL = ztable1-roll.

endif.

*****************************************

here after fetching data from ztable1 you are checking IF SY-SBRC = 0

**SY-SUBRC = 0 means SUCCESS but you are using it as FAILURE.

so as per ur coding even if SY_SUBRC = 0 I>E> SUCCESS u are giving error message and leaving list processing which is incorrect...

just swap the actions in IF..ELSE

if sy-subrc NE 0. "FAILURE

message e000.

leave list-processing.

else. " SUCCESS

if not it_ztable1[] is initial.

sort it_ztable1 by roll.

SELECT roll age

FROM ZTABLE2

INTO TABLE it_ZTABLE2

FOR ALL ENTRIES IN it_ZTABLE1

WHERE ROLL = ztable1-roll.

endif.