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

Dynamic select query

Former Member — Fri, 08 Feb 2008 07:37:36 GMT

I have to select some fields from few database tables which are determined at run time. Any pointers to dynamic select query would help.

Regards

Dinesh

Re: Dynamic select query

Former Member — Fri, 08 Feb 2008 08:48:37 GMT

Hi Dinesh,

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.

U can refer this link

http://www.sap-img.com/abap/how-can-we-give-dynamic-table-name-in-select-statement.htm

http://www.itp-consulting.com/dynamic%20SQL%20in%20SAP.pdf

Reward if usefull,

Thanks,

Swati

Re: Dynamic select query

Former Member — Fri, 08 Feb 2008 08:50:25 GMT

Hi,

In this way you can create dynamic internal table. May be the whole code is of no use but will surely help you.

type-pools : abap.

field-symbols: <dyn_table> type standard table,

<dyn_wa>,

<dyn_field>.

data: dy_table type ref to data,

dy_line type ref to data,

xfc type lvc_s_fcat,

ifc type lvc_t_fcat.

selection-screen begin of block b1 with frame.

parameters: p_table(30) type c default 'T001'.

selection-screen end of block b1.

start-of-selection.

perform get_structure.

perform create_dynamic_itab. *********Creates a dyanamic internal table*********

perform get_data.

perform write_out.

form get_structure.

data : idetails type abap_compdescr_tab,

xdetails type abap_compdescr.

data : ref_table_des type ref to cl_abap_structdescr.

Get the structure of the table.

ref_table_des ?=

cl_abap_typedescr=>describe_by_name( p_table ).

idetails[] = ref_table_des->components[].

loop at idetails into xdetails.

clear xfc.

xfc-fieldname = xdetails-name .

xfc-datatype = xdetails-type_kind.

xfc-inttype = xdetails-type_kind.

xfc-intlen = xdetails-length.

xfc-decimals = xdetails-decimals.

append xfc to ifc.

endloop.

endform.

form create_dynamic_itab.

Create dynamic internal table and assign to FS

call method cl_alv_table_create=>create_dynamic_table

exporting

it_fieldcatalog = ifc

importing

ep_table = dy_table.

assign dy_table->* to <dyn_table>.

Create dynamic work area and assign to FS

create data dy_line like line of <dyn_table>.

assign dy_line->* to <dyn_wa>.

endform.

form get_data.

Select Data from table.

select * into table <dyn_table>

from (p_table).

endform.

Write out data from table.

loop at <dyn_table> into <dyn_wa>.

do.

assign component sy-index

of structure <dyn_wa> to <dyn_field>.

if sy-subrc <> 0.

exit.

endif.

if sy-index = 1.

write:/ <dyn_field>.

else.

write: <dyn_field>.

endif.

enddo.

endloop.

Jayant Sahu

Re: Dynamic select query

Former Member — Fri, 30 May 2008 04:37:34 GMT

Thanks to all who replied.

Re: Dynamic select query

Former Member — Fri, 21 Jan 2011 15:25:28 GMT

<<****Dynamically selecting the select list based on check box ***>>

REPORT ZTEST.

TABLES :bseg .

DATA : sel_list(1000) TYPE c.

field-symbols : <fs> type any.

*FIELD-SYMBOLS : <lt_outtab> TYPE ANY TABLE,

<ls_outtab> TYPE ANY,
<l_fld> TYPE ANY.

SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.

SELECT-OPTIONS : so_BUKRS FOR bseg-BUKRS,

so_BELNR FOR bseg-BELNR,

so_GJAHR FOR bseg-GJAHR .

PARAMETERS : p_BUZID AS CHECKBOX ,

p_AUGDT AS CHECKBOX .

SELECTION-SCREEN END OF BLOCK block1.

data : itab type table of string,

wa like line of itab.

wa = 'p_BUZID'.

append wa to itab.

wa = 'p_AUGDT'.

append wa to itab.

loop at itab into wa.

assign (wa) to <fs>.

if <fs> = 'X'.

concatenate wa+2 sel_list into sel_list separated by space.

endif.

endloop.