Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

split dynamic sql

Former Member
0 Kudos

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

6 REPLIES 6

Former Member
0 Kudos

Hi Anuj,

This SQL Looks perfectly OK......Why are you looking to break this????

You might not gain anything from the same...

Former Member
0 Kudos

Hi AJAY,

I hve some Performance issue with single many join sql that's why we r spliting these types of sql.

regards,

Anuj

0 Kudos

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.......

nivin_varkey
Active Participant
0 Kudos

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

Former Member
0 Kudos

Thanx nivin but can u provide me the dynamic design patteren for that type of applications.

Regards,

Anuj

Former Member
0 Kudos

thanx all.