Posted: Sun Nov 25, 2007 10:26 pm Post subject: Using dynamic SELECT statements
The very useful SELECT statement could be fully dynamic from release 6.10 and up.
For more information about the dynamic select, you can read the document "[url=http://www.sapnet.ru/examples/dynamic SQL in SAP.pdf]Enhanced ABAP programming with Dynamic Open SQL[/url]" from Adrian G?rler and Ulrich Koch.
Here is an example of a fully dynamic select.
Code:
REPORT zdany_dynamic_select.
* We use some parameters to dynamically control the select, this is not very
* clever but this is just a test program !!
PARAMETER : p_tabnam TYPE tabname DEFAULT 'SFLIGHT',
p_selfl1 TYPE edpline DEFAULT 'CARRID',
p_selfl2 TYPE edpline DEFAULT 'CONNID',
p_selfl3 TYPE edpline DEFAULT 'FLDATE',
p_selfl4 TYPE edpline DEFAULT 'PRICE',
p_selfl5 TYPE edpline DEFAULT 'CURRENCY',
p_where1 TYPE edpline DEFAULT 'PRICE > 300',
p_where2 TYPE edpline DEFAULT 'AND CURRENCY = ''EUR'''.
FIELD-SYMBOLS : <lt_outtab> TYPE ANY TABLE,
<ls_outtab> TYPE ANY,
<l_fld> TYPE ANY.
DATA: lt_where TYPE TABLE OF edpline,
lt_sel_list TYPE TABLE OF edpline,
lt_group TYPE TABLE OF edpline,
l_having TYPE string,
l_wa_name TYPE string,
l_sel_list TYPE edpline,
dref TYPE REF TO data,
itab_type TYPE REF TO cl_abap_tabledescr,
struct_type TYPE REF TO cl_abap_structdescr,
elem_type TYPE REF TO cl_abap_elemdescr,
comp_tab TYPE cl_abap_structdescr=>component_table,
comp_fld TYPE cl_abap_structdescr=>component.
TYPES: f_count TYPE i.
* Creation of the output table including a non standard field, f_count
* see ABAP FAQ #14 for more information on this topic
struct_type ?= cl_abap_typedescr=>describe_by_name( p_tabnam ).
elem_type ?= cl_abap_elemdescr=>describe_by_name( 'F_COUNT' ).
comp_tab = struct_type->get_components( ).
* We remove the unnecessary fields
LOOP AT comp_tab INTO comp_fld.
IF comp_fld-name <> p_selfl1 AND
comp_fld-name <> p_selfl2 AND
comp_fld-name <> p_selfl3 AND
comp_fld-name <> p_selfl4 AND
comp_fld-name <> p_selfl5.
DELETE TABLE comp_tab WITH TABLE KEY name = comp_fld-name.
ENDIF.
ENDLOOP.
comp_fld-name = 'F_COUNT'.
comp_fld-type = elem_type.
APPEND comp_fld TO comp_tab.
l_wa_name = 'l_WA'.
CREATE DATA dref TYPE HANDLE itab_type.
ASSIGN dref->* TO <lt_outtab>.
CREATE DATA dref TYPE HANDLE struct_type.
ASSIGN dref->* TO <ls_outtab>.
* Creation of the selection fields and the "group by" clause
APPEND p_selfl1 TO lt_sel_list.
APPEND p_selfl1 TO lt_group.
APPEND p_selfl2 TO lt_sel_list.
APPEND p_selfl2 TO lt_group.
APPEND p_selfl3 TO lt_sel_list.
APPEND p_selfl3 TO lt_group.
APPEND p_selfl4 TO lt_sel_list.
APPEND p_selfl4 TO lt_group.
APPEND p_selfl5 TO lt_sel_list.
APPEND p_selfl5 TO lt_group.
APPEND 'COUNT(*) AS F_COUNT' TO lt_sel_list.
* creation of the "where" clause
APPEND p_where1 TO lt_where.
APPEND p_where2 TO lt_where.
* Creation of the "having" clause
l_having = 'count(*) >= 1'.
* THE dynamic select
SELECT (lt_sel_list)
FROM (p_tabnam)
INTO CORRESPONDING FIELDS OF TABLE <lt_outtab>
WHERE (lt_where)
GROUP BY (lt_group)
HAVING (l_having)
ORDER BY (lt_group).
* display of the results
LOOP AT <lt_outtab> ASSIGNING <ls_outtab>.
LOOP AT comp_tab INTO comp_fld.
ASSIGN COMPONENT comp_fld-name OF STRUCTURE <ls_outtab> TO <l_fld>.
WRITE: <l_fld>.
ENDLOOP.
SKIP.
ENDLOOP.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
