topic Re: Dynamic SQL query in Application Development and Automation Discussions

Dynamic SQL query

Former Member — Thu, 10 Aug 2006 11:27:49 GMT

Hi Experts,

I am displaying a particular record of a table attached to a Transport Request. Based on a transport request, I am generating its list of objects and tables attached to it. Now I need to display a particular record of the table based on its key value and constant name. For that I have to use dyanamic select statement using dynamic tablename and dynamic where clause.

Till now, I have done like this:

S_trkorr is the select-option for Request No./Task No.

if not s_trkorr is initial.

select trkorr strkorr from e070

into table itab_e070

where trkorr in s_trkorr.

*sy-subrc check.

endif.

sort itab_e070.

if not itab_e070[] is initial.

select trkorr

as4pos

pgmid

object

from e071

into table itab_e071

for all entries in itab_e070

where trkorr = itab_e070-trkorr.

*sy-subrc check

endif.

sort itab_e071[].

if not itab_e071[] is initial.

select trkorr

pgmid

object

objname

as4pos

mastertype

mastername

tabkey

from e071k

into table itab_e071k

for all entries in itab_e071

where trkorr = itab_e071-trkorr

and pgmid = itab_e071-pgmid

and object = itab_e071-object.

*sy-subrc check

endif.

sort itab_e071k.

I need to take the table name that gets stored in objname/mastername and the object name that gets stored in the field TABKEY(which has the particular record no. that is the table line entry and the constant name) to fetch the data stored in that particular record.

How to go about it?

I have no idea about dynamic SQL Queries. What is it objective and why is it used? Any material on it.

Thanks in advance,

Sangeeta.

Re: Dynamic SQL query

Former Member — Thu, 10 Aug 2006 11:37:26 GMT

Hope this program helps you for dynamic table access. you could declare a variable of type dd02l-tabname like p_tab in the below program and further use it for selection.

REPORT ztabaccess .

DATA: a_table_line TYPE REF TO data.
DATA: table_lines  TYPE STANDARD TABLE OF REF TO data.
DATA: c TYPE cursor.
FIELD-SYMBOLS: <line>  TYPE ANY.
FIELD-SYMBOLS: <field> TYPE ANY.

PARAMETERS: p_tab TYPE dd02l-tabname.

START-OF-SELECTION.

  OPEN CURSOR c FOR SELECT * FROM (p_tab)
       ORDER BY PRIMARY KEY.
  DO.
    CREATE DATA a_table_line TYPE (p_tab).
    ASSIGN a_table_line->* TO <line>.
    FETCH NEXT CURSOR c INTO <line>.
    IF sy-subrc NE 0.
      CLOSE CURSOR c.
      EXIT.
    ENDIF.
    APPEND a_table_line TO table_lines.
  ENDDO.

  LOOP AT table_lines INTO a_table_line.
    ASSIGN a_table_line->* TO <line>.
    NEW-LINE.
    DO 6 TIMES.                   
      CHECK sy-index > 1.
      ASSIGN COMPONENT sy-index OF STRUCTURE <line> TO <field>.
      IF sy-subrc NE 0.
        EXIT.
      ENDIF.
      WRITE: <field>.
    ENDDO.
  ENDLOOP.

~thomas.

Message was edited by: Thomas Mann

Re: Dynamic SQL query

andreas_mann3 — Thu, 10 Aug 2006 11:38:26 GMT

hi,

use:

SELECT (fields) FROM (itab-tabname) INTO <fs>

WHERE (wtab).

Re: Dynamic SQL query

Former Member — Thu, 10 Aug 2006 11:53:32 GMT

Hi,

Check this Rich's Blog on Dynamic internal tables etc..

/people/rich.heilman2/blog/2005/07/27/dynamic-internal-tables-and-structures--abap

Regards

vijay