10-19-2008 2:22 PM
Hi All,
I hve a different Requirement, i'm creating a rfc which execute the sql which is given by the client side n these r long single dynamic sql.
MY work is to write a (dynamic)code which split this sql in different small sql acc to their join combinations.
Let's take a exmple suppose i hve a long sql like
SELECT MARCMATNR marcwerks FROM MARC INNER JOIN MARD ON MARDWERKS = MARCWERKS INNER JOIN MARA ON MARCMATNR = MARAMATNR AND MARDMATNR = MARAMATNR WHERE MARCWERKS ='1000' AND MARAMATKL = '001'.
which used three tables MARA,MRAC,MARD in diff join conditions then I hve to split these in three diff sql for MARA MARC & MARD that gives same result as first one.
So wht should be keep in mind to design that issue that these splits sql gives same result as gives single one.
Regards
Anuj
10-19-2008 2:35 PM
Hi Anuj,
This SQL Looks perfectly OK......Why are you looking to break this????
You might not gain anything from the same...
10-19-2008 2:44 PM
Hi AJAY,
I hve some Performance issue with single many join sql that's why we r spliting these types of sql.
regards,
Anuj
10-19-2008 3:20 PM
Hi,
You can break this into Two different SQL's.
First, Inner Join between MARC & MARD, for Plant 1000, and store it in Itab.
Second, Select on MARA, with for all entries itab, matnr = itab-matnr and matkl = '001', store this in itab1.
Eliminate all the MATNR Records of itab, which are not there in itab1.
Take care that your Itab should never have more a large amount of Data, else FOR ALL ENTIRES may result in DUMP.......
10-19-2008 6:46 PM
hi anuj,
i would suggest that you drive your selection from MARA, as the number of entries on MATKL would be lesser as compared to MARC and MARD and morevover MARA has an index on MATKL.
so the solution would be like, first select the required article from MARA on condition MATKL into an internal table, Then do a join on MARC and MARD and perform a selection on "for all entries" of articles in the above internal table (this would ensure that MARC and MARD have selection on primary index).
I am getting a fast result doing this on nearly 500000 articles, so i think this should work pretty well for you too.
let me know, the result after you do this.
Regards,
NJ
10-20-2008 6:08 AM
Thanx nivin but can u provide me the dynamic design patteren for that type of applications.
Regards,
Anuj
11-22-2008 6:06 AM