topic Dynamic WHERE statement in Application Development and Automation Discussions

Dynamic WHERE statement

Former Member — Wed, 02 Jul 2008 19:39:16 GMT

Hello

Maybe some one could tell me how to optimize my dynamic WHERE statement. The statement works but could some one show me another to formulate my dynamic WHERE statement?

2) Secondly I have tried to pass the Destination as a parameter but I have problems because the parameter could not be understood by the FM. Can some one show me how to achieve this also?

This is my code:

REPORT ztest_dynamic_where.
DATA: t_options LIKE TABLE OF rfc_db_opt WITH HEADER LINE.
DATA: t_fields LIKE TABLE OF rfc_db_fld WITH HEADER LINE.
DATA: t_data LIKE TABLE OF tab512 WITH HEADER LINE.
DATA: w_unam0(8)  TYPE c VALUE 'UNAM NE',
      w_unam1(3)    TYPE c VALUE 'SAP',
      w_unam3(4)    TYPE c VALUE 'SAP*',
      w_unam2(4)    TYPE c VALUE 'DDIC',
      w_unama(9)   TYPE c VALUE 'ALEREMOTE',
      w_subca       TYPE c VALUE 'S',
      w_unams(7)    TYPE c VALUE 'SUBC NE',
      w_and(3)      TYPE c VALUE 'AND'.

SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME TITLE text-b01.
PARAMETER p_tab LIKE dd02l-tabname DEFAULT 'TRDIR'.
SELECTION-SCREEN END OF BLOCK b01.

*-----------------------------------------------------------------------
START-OF-SELECTION.
*-----------------------------------------------------------------------
  CONCATENATE w_unam0 '''unam1''' INTO t_options SEPARATED BY space.
  REPLACE 'unam1' WITH w_unam1 INTO t_options.
  APPEND t_options.
  CONCATENATE w_and w_unam0  '''unam2''' INTO t_options SEPARATED BY space.
  REPLACE 'unam2' WITH w_unam2 INTO t_options.
  APPEND t_options.
  CONCATENATE w_and w_unam0  '''unam3''' INTO t_options SEPARATED BY space.
  REPLACE 'unam3' WITH w_unam3 INTO t_options.
  APPEND t_options.
  CONCATENATE w_and w_unam0  '''unama''' INTO t_options SEPARATED BY space.
  REPLACE 'unama' WITH w_unam2 INTO t_options.
  APPEND t_options.
  CONCATENATE w_and w_unams  '''subca'''  INTO t_options SEPARATED BY space.
  REPLACE 'subca' WITH w_subca INTO t_options.
  APPEND t_options.

PERFORM data_selection.
*&---------------------------------------------------------------------*
*&      Form  DATA_SELECTION
*&---------------------------------------------------------------------*
FORM data_selection .
  CALL FUNCTION 'RFC_READ_TABLE' DESTINATION 'TEST_I07'
    EXPORTING
      query_table              = p_tab    
*   DELIMITER                  = ' '
*   NO_DATA                    = ' '
*   ROWSKIPS                   = 0
*   ROWCOUNT                   = 0
    TABLES
      OPTIONS                  = t_options
      fields                   = t_fields
      data                     = t_data
 EXCEPTIONS
   TABLE_NOT_AVAILABLE        = 1
   TABLE_WITHOUT_DATA         = 2
   OPTION_NOT_VALID           = 3
   FIELD_NOT_VALID            = 4
   NOT_AUTHORIZED             = 5
   DATA_BUFFER_EXCEEDED       = 6
   OTHERS                     = 7
            .
  IF sy-subrc <> 0.
    WRITE: 'Test failed'.
ELSE.
  WRITE 'Test was successful'.
  ENDIF.
ENDFORM.

I would really appreciate if some one can show me perhaps a better way to generate a dynamic WHERE statement and also how to pass the Destination as a parameter.

Thank you all for your tips

Nadin.

Re: Dynamic WHERE statement

former_member156446 — Wed, 02 Jul 2008 19:41:35 GMT

Hi Ram check this sample code about dynamic where clause.

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.


data: vl_des type string.
vl_des = p_desn. "ur parameter.
....
CALL FUNCTION 'RFC_READ_TABLE' 
 DESTINATION 'Vl_des'
    EXPORTING....

Re: Dynamic WHERE statement

Former Member — Wed, 02 Jul 2008 20:06:37 GMT

Try this code:


report ztest_sourav1.
DATA: where_tab TYPE STANDARD TABLE OF char72 INITIAL SIZE 0,
      wa_where_tab TYPE char72,
      i_sbook TYPE STANDARD TABLE OF sbook INITIAL SIZE 0.
DATA: cx_sy_dynamic_osql_syntax TYPE REF TO cx_sy_dynamic_osql_syntax,
      v_oref TYPE REF TO cx_root,
      v_text TYPE string.
wa_where_tab = 'CARRID = ''AA'''.
APPEND wa_where_tab TO where_tab.
wa_where_tab = 'AND'.
APPEND wa_where_tab TO where_tab.
wa_where_tab = 'CONNID = ''0017'''.
APPEND wa_where_tab TO where_tab.
IF where_tab[] IS NOT INITIAL.

  TRY.
      SELECT *
        FROM sbook
        INTO TABLE i_sbook
        WHERE
        (where_tab).
    CATCH cx_sy_dynamic_osql_syntax INTO v_oref.
      v_text = v_oref->get_text( ).
      WRITE /1 v_text.
  ENDTRY.
  IF sy-subrc = 0 and i_sbook is not INITIAL.

    DATA: l_gr_alv TYPE REF TO cl_salv_table.
    TRY.
        CALL METHOD cl_salv_table=>factory
          IMPORTING
            r_salv_table = l_gr_alv
          CHANGING
            t_table      = i_sbook.
      CATCH cx_salv_msg .
    ENDTRY.

    l_gr_alv->display( ).

  ENDIF.
ENDIF.