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: 

Absolute dynamic select query with dynamic join and where

agnihotro_sinha2
Active Contributor
0 Kudos

Has anyone ever tried creating an absolutely dynamic SELECT query with dynamic Join and Where conditions.

I have a requirement of creating such a query in an Utility Class, and i have written the code. But its throwing my sysntax errors.

Please let me know where am I going wrong OR is it really possible to create such a dynamic Query??



    SELECT (FIELDS) INTO TABLE IT_TABLES 
      FROM ( (ME->TABLE1)  inner join ( me->table2 )
      on ( on_condition ) )
      WHERE (me->where_fields).

Ags.

1 ACCEPTED SOLUTION

0 Kudos

It worked for me in a following way:

select * into corresponding fields of table <result_table>
        from (join_string)
        where (l_where).

Where the contents of join_string were dynamically build using concatenation. So it will be something like

concatenate ME->TABLE1 'as a INNER JOIN' me->table2 'as b ON (' into join_string separated by space.
<...> 
add here matching/reference colums, something like

concatenate 'a~' me->TABLE1_JOIN_COL into temp1.
concatenate 'b~' me->TABLE2_JOIN_COL into temp2.
concatenate join_string temp1 '=' temp2 into join_string separated by space.
 <...>

concatenate join_string ')' into join_string separated by space.

And then use similar approach for l_where variable.

1 REPLY 1

0 Kudos

It worked for me in a following way:

select * into corresponding fields of table <result_table>
        from (join_string)
        where (l_where).

Where the contents of join_string were dynamically build using concatenation. So it will be something like

concatenate ME->TABLE1 'as a INNER JOIN' me->table2 'as b ON (' into join_string separated by space.
<...> 
add here matching/reference colums, something like

concatenate 'a~' me->TABLE1_JOIN_COL into temp1.
concatenate 'b~' me->TABLE2_JOIN_COL into temp2.
concatenate join_string temp1 '=' temp2 into join_string separated by space.
 <...>

concatenate join_string ')' into join_string separated by space.

And then use similar approach for l_where variable.