SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Using dynamic SELECT statements



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> Dynamic Programming | Динамическое программирование
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Sun Nov 25, 2007 10:26 pm    Post subject: Using dynamic SELECT statements Reply with quote

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.
 
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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> Dynamic Programming | Динамическое программирование All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
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.