02-08-2008 7:37 AM
Hi
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
02-08-2008 8:48 AM
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
02-08-2008 8:50 AM
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
05-30-2008 5:37 AM
01-21-2011 3:25 PM
<<****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.