This website is not affiliated with, sponsored by, or approved by SAP AG.

0036 - Dynamic Select Statements

Moderators: Snowy, thx4allthefish, Rich, ilya

0036 - Dynamic Select Statements

Postby Rich » Sat Mar 28, 2009 8:44 am

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 "Enhanced ABAP programming with Dynamic Open SQL" from Adrian Görler and Ulrich Koch.


Here is an example of a fully dynamic select.

Code: Select all
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.

struct_type = cl_abap_structdescr=>create( comp_tab ).
itab_type   = cl_abap_tabledescr=>create( struct_type ).

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.


(Courtesy Dany Charbonneau)
Regards

Rich

Image
Abap KC:http://www.richard-harper.me.uk/Kb
SFMDR:http://www.se37.com
Rich
 
Posts: 7112
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool

Return to ABAPers

Who is online

Users browsing this forum: No registered users and 1 guest





loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.