topic Re: Want to create dynamic SQL with table join in Application Development and Automation Discussions

Want to create dynamic SQL with table join

Former Member — Mon, 29 Jan 2007 23:23:28 GMT

I want to create a dynamic SQL query so that the user can enter any two table names on the selection screen and two field names that he wants to join. After which i should be able to dynamically generate the SQL (native or open) and show the result as report. I have already read the forum threads and know how to create dynamic SQL for single table, what i m struggling with is how to do inner join. I know i can use nested select but that will make the query very slow because i want to extend it to more than 2 tables later.

Will give points to useful answer for sure, thanks for reading.

Re: Want to create dynamic SQL with table join

Former Member — Tue, 30 Jan 2007 00:52:04 GMT

You should read my weblog to get an idea -:)

Greetings,

Blag.

Re: Want to create dynamic SQL with table join

Former Member — Tue, 30 Jan 2007 00:53:01 GMT

Hi,

Following is a piece of code which I have used in my program.

DATA: ws_flds(72) TYPE c.

DATA: ftab LIKE TABLE OF ws_flds.

ws_flds = 'rbukrs rprctr racct ryear '.

APPEND ws_flds TO ftab.

SELECT (ftab)

INTO CORRESPONDING FIELDS OF TABLE it_grp_glpca

FROM glpca FOR ALL ENTRIES IN i_cert_item

WHERE kokrs = c_kokrs

AND rldnr = '8A'

AND rrcty IN ('0','2')

AND rvers = '000'

AND rbukrs = i_cert_item-bukrs

AND ryear = p_ryear

AND rprctr = i_cert_item-prctr

AND racct = i_cert_item-saknr

AND ( poper BETWEEN '001' AND ws_poper )

AND aufnr IN s_aufnr

AND kostl IN s_kostl

AND rfarea IN s_fkber

AND rmvct IN s_rmvct

AND sprctr IN s_sprctr

AND ( racct BETWEEN c_low AND c_high ).

You can now pass your table name as (p_table) or append fieldnames to the internal table (ftab). if it is join then you can append this table like a_{bukrs a}saknr..etc.

Regards

Subramanian

Re: Want to create dynamic SQL with table join

Former Member — Tue, 30 Jan 2007 17:16:18 GMT

Hi Alvaro,

Your function module seems really useful. But when i put in the table names as

VBAK and VBAP, filter table entries as vbeln and posnr and where_table as VBELN EQ '1500000267'.

I get a runtime error "LOAD_PROGRAM_NOT_FOUND" on line

005500 if r_oo_class is initial.

005510 perform (l_form) in program (l_name).

> else.

005530 perform (l_form) in program (l_name) using r_oo_class

of include LSKBHF06.

I have tried hard to figure out the problem but cant, could you please help

Re: Want to create dynamic SQL with table join

Former Member — Tue, 30 Jan 2007 17:42:09 GMT

GOOD

GO THROUGH THIS LINK,i hope this ll help you to solve your problem

/people/ravishankar.rajan/blog/2006/12/21/building-dynamic-sql-statements-using-the-clrswhere-class

thanks

mrutyun^