topic Re: joining tables fields in Application Development and Automation Discussions

joining tables fields

Former Member — Thu, 26 Feb 2009 10:56:15 GMT

hi all,

how to join 7 different table fields to final data? i need to pass all 7 table fields to final .

can u guys help me out thru code.

TYPES : BEGIN OF TY_FINAL,

LDDAT TYPE VBEP-LDDAT,

WERKS TYPE VBAP-WERKS,

LIFSK TYPE VBAK-LIFSK,

BSTKD TYPE VBKD-BSTKD,

AUART TYPE VBAK-AUART,

AUDAT TYPE VBAK-AUDAT,

VBELN TYPE VBAK-VBELN,

POSNR TYPE VBAP-POSNR,

KUNNR TYPE VBPA-KUNNR,

MATNR TYPE VBAP-MATNR,

WMENG TYPE VBEP-WMENG,

NETWR TYPE VBAK-NETWR,

AUGRU TYPE VBAK-AUGRU,

FAKSK TYPE VBAK-FAKSK,

ARKTX TYPE VBAP-ARKTX,

VKGRP TYPE VBAK-VKGRP,

VKBUR TYPE VBAK-VKBUR,

LIFNR TYPE VBPA-LIFNR,

LIFNR TYPE VBPA-LIFNR-LP,

PERNR TYPE VBPA-PERNR,

LPRIO TYPE VBAP-LPRIO,

INCO1 TYPE VBKD-INCO1,

ABLAD TYPE KUWEV-ABLAD,

BSTDK TYPE VBKD-BSTDK,

ZTERM TYPE VBKD-ZTERM,

WAERK TYPE VBAK-WAERK,

ERNAM TYPE VBAK-ERNAM,

ABGRU TYPE VBAP-ABGRU,

LFGSK TYPE VBUK-LFGSK,

PRSDT TYPE VBKD-PRSDT,

VDATU TYPE VBAK-VDATU,

CHARG TYPE VBAP-CHARG,

VSTEL TYPE VBAP-VSTEL,

LGORT TYPE VBAP-LGORT,

END OF TY_FINAL.

these r da fields of different table

thanks&regards,

sumanth reddy

Edited by: sumanth reddy on Feb 26, 2009 4:37 PM

Re: joining tables fields

tarangini_katta — Thu, 26 Feb 2009 16:39:04 GMT

Hi Sumanth,

Use the standarad FM to get all the details according to u r selection criteria i.e the good way i think.

Thanks

Re: joining tables fields

Former Member — Fri, 27 Feb 2009 04:23:14 GMT

check this thread

it might be helpful to you.

thanks

SAchin

Re: joining tables fields

Former Member — Fri, 27 Feb 2009 04:38:55 GMT

Hi Sumanth...

You have to use SQVI transaction for joining the tables. Its the easy way to find the key fields in a table for joining among them. 1) Go to SQVI 2) Enter the text TEST in Quick view and Create 3) Enter the title text TABLE JOIN and select TABLE JOIN under the data source option and enter 4) On next screen you find Insert table option, click the option and enter the table name which u want to join 5) Again click the same option for entering the next table 6) Automatically u find the key fields marked on your screen.

Regards,

Sathish Donthula

Re: joining tables fields

Former Member — Fri, 27 Feb 2009 05:19:59 GMT

hii satish,

thanks for u r response ,can u explain in me detail.i added the tables and how to join all the fields into single query,plz help me out.

thanks & regards,

sumanth reddy

Re: joining tables fields

Former Member — Mon, 02 Mar 2009 08:43:47 GMT

hi,

Goto SQ01(Tcode) here press QUICK VIEWER-> Enter Quick View name as TEST -> Press Create.

Here Enter TITILE as TEST and select DATA SOURCE as Table Join and keep layout mode as it is -> Press Enter.

Here you can enter any MM and FI tables to know which fields are common to make inner joins.

Hope this works for you.

Thanks........

Re: joining tables fields

Sathya_Gunasekaran — Tue, 03 Mar 2009 04:59:28 GMT

Hello Sumanth,

If my understanding is right... define separate itabs having fields from diff. db tables.

populate them based on Ur selection criteria.

Define Ur final internal table.

Based on the key field (connecting/common field) for the tables move records to the final table.

eg.,

Loop at itab1.

move itab1-field1 to itab_finalfiled1.

read itab2 where keyfield = itab1-keyfield.

move itab2-field2 to itab_finalField2.

append itab_final.

endloop.

Hope this helps.

Regards

Sathya

Re: joining tables fields

Former Member — Tue, 03 Mar 2009 05:28:14 GMT

Dear Sumanth,

For better performance and huge tables links use for all entries....

1. create individual internal tables for 7 tables

2. create another internal table for final entries

3. write the select as shwon below...

SELECT lifnr name1 FROM lfa1 INTO TABLE lt_vennum FOR ALL ENTRIES IN lt_matdocitm1

WHERE lifnr = lt_matdocitm1-lifnr.

SELECT lifnr ekorg xersy FROM lfm1 INTO TABLE lt_venorg FOR ALL ENTRIES IN lt_vennum

WHERE lifnr = lt_vennum-lifnr.

SELECT mblnr wrbtr FROM zmvend_inv_amnt INTO TABLE lt_invamt FOR ALL ENTRIES IN lt_matdocitm WHERE mblnr = lt_matdocitm-lfbnr

AND mjahr = lt_matdocitm-mjahr.

SELECT mblnr wrbtr FROM zmvend_inv_amnt APPENDING TABLE lt_invamt FOR ALL ENTRIES IN lt_matdocitm

WHERE mblnr = lt_matdocitm-mblnr

AND mjahr = lt_matdocitm-mjahr.

get the all entris into final table...

4. this the way to get into one internak table....

KYSetty

Re: joining tables fields

Former Member — Fri, 06 Mar 2009 10:55:48 GMT

declare 7 internal tables for ur 7 tables and one final internal table to hold all the data.

select vbeln vbtyp erdat kunnr vkorg vtweg spart from vbak

into table i_vbak.........

select vbeln posnr matnr arktx werks kwmeng from vbap

into table i_vbap for all entries in i_vbak where vbeln = i_vbak-vbeln.....

select vbelv posnv erdat vbeln posnn matnr vbtyp_n from vbfa

into table i_vbfa for all entries in i_vbap where vbelv = i_vbap-vbeln....

select vbeln posnr rfsta from vbup into table i_vbup for all entries in i_vbfa

where vbeln = i_vbfa-vbelv......

select vbeln posnr kwmeng from vbap into table i_vbap1 for all entries in i_vbfa

where vbeln = i_vbfa-vbeln.....

select vbeln posnr gbsta from vbup into table i_vbup1 for all entries in i_vbap1

where vbeln = i_vbap1-vbeln.....etc

and while looping into the final internal table

loop at i_vbap.

move i_vbap-vbeln to i_final-vbeln.

move i_vbap-posnr to i_final-posnr.

read table i_vbfa with key vbelv = i_vbap-vbeln

posnv = i_vbap-posnr.

if sy-subrc = 0.

move i_vbfa-vbeln to i_final-vbeln1.

move i_vbfa-posnn to i_final-posnn.

endif.

read table i_vbap1 with key vbeln = i_final-vbeln1

posnr = i_final-posnn.

if sy-subrc = 0.

endif.

read table i_vbap1 with key vbeln = i_final-vbeln1

posnr = i_final-posnn.

if sy-subrc = 0.

endif.

read table i_vbup with key vbeln = i_vbap-vbeln

posnr = i_vbap-posnr.

if sy-subrc = 0.

endif.

read table i_vbak with key vbeln = i_vbap-vbeln

binary search.

if sy-subrc = 0.

endif.

append i_final.

endloop.

u have to read all the internal tables based on the key common fields and put into final itab.

Regards