05-23-2011 6:53 AM
Hi all,
my requirement is to make a relationship among 11 tables to make a database for my project. I break those tables into 3 to 4 tables and applied join on them and at last i got THREE internal tables. now I want to fetch the data from each of the 3 internal table and store them in a single internal table.
when i tried to apply a LOOP on one internal table and append the wa to the main internal table, the data actually not appended in the main table..
SELECT sanalysis_id tmasterprocess_id ucountry_name vetvxformat_id w~process_comments
wcountry_comments wetvx_remarks INTO CORRESPONDING FIELDS OF TABLE itab_analy
FROM ( ( ( ( zanaly_process AS s INNER JOIN zmast_process AS t ON smasterprocess_id = tmasterprocess_id )
INNER JOIN zcountry_data AS u ON scountryid = ucountryid )
INNER JOIN zetvx_table AS v ON setvxformat_id = vetvxformat_id )
INNER JOIN zcomm_table AS w ON scomments_id = wcomments_id ).
IF sy-subrc <> 0.
WRITE:/ 'No Records Found'.
ENDIF.
LOOP AT itab_analy INTO itab_analy.
APPEND itab_analy to itab_final.//// here is the problem..i don't get any data from itab_final..
*MODIFY itab_final FROM wa.
WRITE:/ 'what happend',itab_final-analysis_id.
ENDLOOP.
thanks in advance for help...
05-23-2011 6:56 AM
Hi,
Please check if teh structure for the two tables is same.
APPEND itab_analy to itab_final.
itab_analy and itab_fianl should have same set of fields and same sequence.
Also, here you are appending the entire table inside the loop and not the workarea.
05-23-2011 7:09 AM
HI,
make it simple by declaring a WA.
Loop at itab into WA..
.Check the structure same or not.
.Check whether the select statement actually fetching data or not.
05-23-2011 7:38 AM
Hi ss_sutar03 ;
Please check your code.
LOOP AT itab_analy INTO gs_itab_analy."*itab_analy*.
MOVE-CORRESPONDING gs_itab_analy TO itab_final.
"APPEND gs_itab_analy to itab_final.//// here is the problem..i don't get any data from itab_final..
append itab_final.
*MODIFY itab_final FROM wa.
WRITE:/ 'what happend',itab_final-analysis_id.
ENDLOOP.
Best regards.
05-23-2011 7:38 AM
I think your code is working but it appears not to because you arer wrinting from the HEADER of the final table which has not been changed to try and 'prove' your code worked. It will never have the correct value and so it appears wrong.
Change your code to this (as long as the tow tables have the same structure):
SELECT sanalysis_id tmasterprocess_id ucountry_name vetvxformat_id w~process_comments
wcountry_comments wetvx_remarks INTO CORRESPONDING FIELDS OF TABLE itab_analy
FROM ( ( ( ( zanaly_process AS s INNER JOIN zmast_process AS t ON smasterprocess_id = tmasterprocess_id )
INNER JOIN zcountry_data AS u ON scountryid = ucountryid )
INNER JOIN zetvx_table AS v ON setvxformat_id = vetvxformat_id )
INNER JOIN zcomm_table AS w ON scomments_id = wcomments_id ).
IF sy-subrc 0.
WRITE:/ 'No Records Found'.
ENDIF.
LOOP AT itab_analy INTO itab_final.
APPEND itab_final.
WRITE:/ 'what happend',itab_final-analysis_id.
ENDLOOP.
05-23-2011 8:52 AM
You want one final internal table, so why not join 11 tables in one go, if the key relationships are straightforward and the tables contain not too many rows? My "record" so far is 10 tables, which works very stable since years, I'm sure you can top this.
Such a join is quite complex and needs to be implemented properly, but combining the results of three joins of 3 to 4 tables each does not sound less complex to me.
Thomas
05-23-2011 9:07 AM
dear thomas,
thanks for ur valuable reply.. actually at first i thought of doing a complete join on 11 tables but i stepped back bcoz my seniors told me that it may cause performance issue..
the tables may be having a lots of data.. so can i use INNER JOIN on 11 tables at one go...? it may cause low performance
please suggest me ..
thanks in advance...
05-23-2011 9:31 AM
> My "record" so far is 10 tables, which works very stable since years.
Wowwie !!!
Once i had joined 6 tables & had to scratch my head for a couple of hours & conduct countless test runs to get it correct.
Add to it the hard time i had convincing the QA reviewer that joining these tables was more performance enhanced than FAE.
@OP: Don't blindly beleive what your "seniors" say! Try out all the possible combinations & figure out which one is the best performance-wise.
Cheers,
Suhas
05-23-2011 9:35 AM
It can cause performance problems as much as many other things can.
If you have a properly constructed join statement, then the CBO (cost based optimizer) of the DB will determine the best access path based on circumstances (e.g. select-options provided, table statistics, etc.). If you break this apart, then you are losing that flexibility, and you have follow-on problems like combining the data back into one internal table, which also can cause performance problems.
Now you got to ask yourself, who is smarter when it comes to retrieving data in an optimal way: you, your seniors or the CBO?
You could give it a try and compare the runtimes, like Suhas suggested. Of course there is a chance that it does not work as intended, then blame it on me
Thomas
05-23-2011 10:32 AM
hi..
Check first whether both the table structure are same...
if it is same...rather than appending through the loop, try this one...
ITAB_FINAL[ ] = ITAB_ANALY[ ].
Edited by: Kalidass Etienne.S on May 23, 2011 3:02 PM
05-23-2011 11:42 AM
Hi all,
Thank you all for your valuable suggestions. atlast I successfully completed making a relation among 11 tables. at this point I declare this thread to be closed.... I used 3 different joins for every 3 to 4 table and then put all the 3 table datas into one table... and got the solution...
Thnaks again to all....
05-23-2011 11:44 AM