Search on this Website

Tuesday, December 11, 2007

How to Use Dynamic SQL in ABAP report

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.


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.


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:
TYPE ANY,
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 .

SELECT *
FROM (p_tab) UP TO 10 ROWS
INTO .

NEW-LINE.
DO.
* Write all the fields in the record
ASSIGN COMPONENT sy-index
OF STRUCTURE
TO .
IF sy-subrc <> 0.
EXIT.
ENDIF.
WRITE .
ENDDO.
ENDSELECT.


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.

No comments: