Dynamic Selection

[Former Member]

Hi,

Please help in Using dynamic SELECT statements

Thanks,

Diwakar.

[Former Member]

Hi,

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

http://help.sap.com/saphelp_nw04/helpdata/en/67/93b80914a911d2953c0000e8353423/content.htm

Dynamic fields in select statement: -

http://www.saptechies.com/dynamic-fields-in-select-statement/

Useful link on dynamic open SQL statements: -

http://www.susanto.id.au/papers/DynOpenSQL.asp

First, declare an internal table with a single text field

example:

DATA: BEGIN OF GT_CONDITION OCCURS 0,

LINE,

END OF GT_CONDITION.

Then insert or concatenate in the table the conditions as a text

For example:

gt_condition-line = ‘mandt eq ‘000’‘.

append gt_condition.

Note: In order to insert the quotations in the field, you’ll need to do something like this:

concatenate ‘mandt eq’ ‘’‘’ ‘000’ ‘’‘’ into gt_condition-line.

And the selection can be done as follows:

select * from table where (gt_condition).

endselect.

Reward points for helpful answers.

Regards,

[former_member156446]

Hi Diwakar

check this code:

Display of flight connections after input of airline and flight number:
PARAMETERS: carr_id TYPE spfli-carrid,
            conn_id TYPE spfli-connid.
 
DATA:       where_clause TYPE  STRING,
            and(4),
            wa_spfli TYPE spfli.
 
IF carr_id IS NOT INITIAL.
  CONCATENATE 'CARRID = ''' carr_id '''' INTO where_clause.
  and = ' AND'.
ENDIF.
IF conn_id IS NOT INITIAL.
  CONCATENATE where_clause and ' CONNID = ''' conn_id ''''
    INTO where_clause.
ENDIF.
SELECT * FROM spfli INTO wa_spfli WHERE (where_clause).
  WRITE: / wa_spfli-carrid, wa_spfli-connid, wa_spfli-cityfrom,
           wa_spfli-cityto, wa_spfli-deptime.
ENDSELECT. 

Award points if helpful

[Former Member]

Hi diwakar,

try this link

http://help.sap.com/saphelp_nw70/helpdata/en/67/93b80914a911d2953c0000e8353423/content.htm

[Former Member]

Hi,

The very useful SELECT statement could be fully dynamic from release 6.10 and up.

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 selectSELECT (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.

Reward Points if found helpfull..

Cheers,

Chandra Sekhar.