dynamic query in ABAP

[Former Member]

Can we use dynamic query in ABAP?

[Former Member]

Yes. we can write dynamic queries in ABAP.

[Former Member]

how? can u give me some sample query?

[Former Member]

See the example below.

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.

<b>Please mark points if helpful.</b>

Message was edited by: Thomas Mann

[RichHeilman]

What is the requirement? Dynamic WHERE clause, or dynamic table?

Regards,

Rich Heilman

[Former Member]

Here is a dynamic WHERE clause. Change the sales order number to be one in your VBAK table.

REPORT Zsandbox_prog .

tables: vbak.

parameters: p_val(30) default 'vbeln = ''0000161644'''.

select single * from vbak into vbak where (p_val).

write: / vbak-vbeln, vbak-kunnr.

[Former Member]

hi Rich Heilman

Actually dynamic where clause.

am writing RFC in which there are two import parameters.

i.e. im_pertype & im_keyfig.

now values are passing in rfc as

im_pertype = 'CALYEAR','CONTRYEAR',....

im_keyfig = 'CALENDARHRS','PENSIONHRS',...

Now i have to select data from table compairing these values.So how can i do it?

[Former Member]

1. Dynamic where clause

You can use an internal table to build a dynamic where clause:

data: where_tab(30) occurs 1 with header line,

where_clause(30) type c.

• Build the where clause. Will look like this when finished

• WHERE ZAFSTMD02 = 'X' AND rbusa = '5145'

• With a constant, result: ZAFSTMD01 = 'X'

concatenate 'ZAFSTMD' zcostcheck-zmaaned ' = ''X''' into where_clause.

• Append to internal table where_tab

append where_clause to where_tab.

• With a variable, result: AND rbusa = '5145'

concatenate 'AND rbusa = ' '''' i_tab-zgsber ''''

append where_clause to where_tab.

• Select

select * from zcostfreq

where (where_tab).

endselect.

Note that you can combine static and dynamic where clauses:

select * from zcostfreq

where bukrs = '2021' AND

(where_tab).

endselect.

2. Using a dynamic table name

This report prints the number og entries in a table. The table name is

specified by a parameter.

data:

l_count type i.

parameters:

p_tab type tabname.

start-of-selection.

select count(*) from (p_tab) into l_count.

write: / 'Number of entries in table ', p_tab, l_count.

3. Dynamic retrieval and writing of data

In this example, data is retrieved from the table selected on the selection

screen, and the contents of the

table is written to the screen.

DATA:

• Create variable that can contain referecene to any data

dataref TYPE REF TO data.

FIELD-SYMBOLS:

<row> TYPE ANY,

<component> TYPE ANY.

PARAMETERS:

p_tab TYPE tabname.

START-OF-SELECTION.

• Create a workarea for the tabel selected on the selection screen

CREATE DATA dataref TYPE (p_tab).

• The variable dataref cannot be accessed directly, so a field symbol is

• used

ASSIGN dataref->* TO <row>.

SELECT *

FROM (p_tab) UP TO 10 ROWS

INTO <row>.

NEW-LINE.

DO.

• Write all the fields in the record

ASSIGN COMPONENT sy-index

OF STRUCTURE <row>

TO <component>.

IF sy-subrc <> 0.

EXIT.

ENDIF.

WRITE <component>.

ENDDO.

ENDSELECT.

4. Dynamic SELECT

TYPES:

BEGIN OF st_bseg,

bukrs LIKE bseg-bukrs,

belnr LIKE bseg-belnr,

dmbtr LIKE bseg-dmbtr,

END OF st_bseg.

DATA:

sel_list TYPE STANDARD TABLE OF edpline,

li_bseg TYPE STANDARD TABLE OF st_bseg,

l_bseg TYPE st_bseg.

START-OF-SELECTION.

APPEND 'bukrs belnr dmbtr' TO sel_list.

SELECT (sel_list)

FROM bseg UP TO 100 ROWS

INTO TABLE li_bseg.

LOOP AT li_bseg INTO l_bseg.

WRITE : / l_bseg-bukrs, l_bseg-belnr, l_bseg-dmbtr.

ENDLOOP.

[Former Member]

Hi,

im_pertype = 'CALYEAR','CONTRYEAR',....
im_keyfig = 'CALENDARHRS','PENSIONHRS',...

If base on above, i do believe IM_PERTYPE and IM_KEYFIG are the TABLEs parameter under your RFC FM. Pls correct me if im wrong.

If in that case, i do not see any problems by loop at the each Table parameter and concatenate them into a variable and supply to dynamic SQL statment later.

eg: I want to use value 'CALYEAR' and 'CONTRYEAR' as my data filtering on field ABC in table ZABC.

data: v_where(255),
      v_end(1).

if not im_pertype[] is initial.
  concatenate 'ABC' 'IN' '(' into v_where separated by   
  space.
endif.

loop at im_pertype.
  at last.
    v_end = ')'.
  endat.

  concatenate v_where im_pertype v_end 
    into v_where separated by space.
endloop.

Select single * from ZABC where (v_where).

cheers.