Absolute dynamic select query with dynamic join and where

agnihotro_sinha2 — Wed, 23 Feb 2011 07:45:18 GMT

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.

Re: Absolute dynamic select query with dynamic join and where

audrius_peseckis — Wed, 23 Feb 2011 08:56:14 GMT

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.

topic Absolute dynamic select query with dynamic join and where in Application Development and Automation Discussions

Absolute dynamic select query with dynamic join and where

Re: Absolute dynamic select query with dynamic join and where