topic Re: split dynamic sql in Application Development and Automation Discussions

split dynamic sql

Former Member — Sun, 19 Oct 2008 13:22:29 GMT

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 MARC_{MATNR marc}werks FROM MARC INNER JOIN MARD ON MARD_{WERKS = MARC}WERKS INNER JOIN MARA ON MARC_{MATNR = MARA}MATNR AND MARD_{MATNR = MARA}MATNR WHERE MARC_{WERKS ='1000' AND MARA}MATKL = '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

Re: split dynamic sql

Former Member — Sun, 19 Oct 2008 13:35:01 GMT

Hi Anuj,

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

You might not gain anything from the same...

Re: split dynamic sql

Former Member — Sun, 19 Oct 2008 13:44:11 GMT

Hi AJAY,

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

regards,

Anuj

Re: split dynamic sql

Former Member — Sun, 19 Oct 2008 14:20:18 GMT

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

Re: split dynamic sql

nivin_varkey — Sun, 19 Oct 2008 17:46:49 GMT

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,

Re: split dynamic sql

Former Member — Mon, 20 Oct 2008 05:08:42 GMT

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

Regards,

Anuj

Re: split dynamic sql

Former Member — Sat, 22 Nov 2008 06:06:29 GMT

thanx all.