Building dynamic SQL statements using the CL_RS_WHERE class

[Former Member]

Recently I discovered a very interesting standard class in SAP ECC 5.0.It is called CL_RS_WHERE.It contains methods which can be effectively used to create dynamic SQL statements.It is primarily used for building BW queries but it works very well in the R/3 environment also.In this blog I would like to demonstrate a small example I did using this class.

REPORT  zdynamic_select.TYPES:  BEGIN OF ty_sales,    vbeln  TYPE vbak-vbeln,            " Sales document    posnr  TYPE vbap-posnr,            " Sales document item    matnr  TYPE vbap-matnr,            " Material number    arktx  TYPE vbap-arktx,            " Short text for sales order item    kwmeng TYPE vbap-kwmeng,           " Order quantity    vkorg TYPE vbak-vkorg,             " Sales organization    kunnr TYPE vbak-kunnr,             " Sold-to party    netwr TYPE vbak-netwr,             " Net Value of the Sales Order  END OF ty_sales.DATA :  gt_sales TYPE STANDARD TABLE OF ty_sales,  wa_sales TYPE ty_sales.DATA: ob_select TYPE REF TO cl_rs_where.DATA: ob_from   TYPE REF TO cl_rs_where.DATA: ob_where  TYPE REF TO cl_rs_where,      gv_source TYPE abapsource.START-OF-SELECTION.*Step 1 : Prepare the select fields.  PERFORM zf_build_select.*Step 2 : Build the from clause for the select  PERFORM zf_build_from.*Step 3 : Build the where clause for the select  PERFORM zf_build_where.*Step 4 : Execute the dynamic select  SELECT (ob_select->n_t_where)      FROM (ob_from->n_t_where)        INTO CORRESPONDING FIELDS OF TABLE gt_sales        WHERE (ob_where->n_t_where).  LOOP AT gt_sales INTO wa_sales.    WRITE :   /5 wa_sales-vbeln,              15 wa_sales-vkorg,              20 wa_sales-kunnr,              40 wa_sales-netwr,              50 wa_sales-posnr,              60 wa_sales-matnr,              70 wa_sales-arktx,              90 wa_sales-kwmeng.  ENDLOOP.*&---------------------------------------------------------------------**&      Form  zf_build_select*&---------------------------------------------------------------------*FORM zf_build_select .  CREATE OBJECT ob_select.*Build the table name/field name combination*Add Sales order header fields  CLEAR gv_source.  CALL METHOD cl_rs_where=>build_tabname_fieldname    EXPORTING      i_tabname   = 'VBAK'      i_fieldname = 'VBELN'      i_sign      = '~'    IMPORTING      e_combined  = gv_source.*Add the where line  CALL METHOD ob_select->add_line    EXPORTING      i_line = gv_source.  CLEAR gv_source.  CALL METHOD cl_rs_where=>build_tabname_fieldname    EXPORTING      i_tabname   = 'VBAK'      i_fieldname = 'VKORG'      i_sign      = '~'    IMPORTING      e_combined  = gv_source.*Add the where line  CALL METHOD ob_select->add_line    EXPORTING      i_line = gv_source.  CLEAR gv_source.  CALL METHOD cl_rs_where=>build_tabname_fieldname    EXPORTING      i_tabname   = 'VBAK'      i_fieldname = 'KUNNR'      i_sign      = '~'    IMPORTING      e_combined  = gv_source.*Add the where line  CALL METHOD ob_select->add_line    EXPORTING      i_line = gv_source.  CLEAR gv_source.  CALL METHOD cl_rs_where=>build_tabname_fieldname    EXPORTING      i_tabname   = 'VBAK'      i_fieldname = 'NETWR'      i_sign      = '~'    IMPORTING      e_combined  = gv_source.*Add the where line  CALL METHOD ob_select->add_line    EXPORTING      i_line = gv_source.*Add Sales order item fields  CALL METHOD cl_rs_where=>build_tabname_fieldname    EXPORTING      i_tabname   = 'VBAP'      i_fieldname = 'POSNR'      i_sign      = '~'    IMPORTING      e_combined  = gv_source.*Add the where line  CALL METHOD ob_select->add_line    EXPORTING      i_line = gv_source.  CLEAR gv_source.  CALL METHOD cl_rs_where=>build_tabname_fieldname    EXPORTING      i_tabname   = 'VBAP'      i_fieldname = 'MATNR'      i_sign      = '~'    IMPORTING      e_combined  = gv_source.*Add the where line  CALL METHOD ob_select->add_line    EXPORTING      i_line = gv_source.  CLEAR gv_source.  CALL METHOD cl_rs_where=>build_tabname_fieldname    EXPORTING      i_tabname   = 'VBAP'      i_fieldname = 'ARKTX'      i_sign      = '~'    IMPORTING      e_combined  = gv_source.*Add the where line  CALL METHOD ob_select->add_line    EXPORTING      i_line = gv_source.  CLEAR gv_source.  CALL METHOD cl_rs_where=>build_tabname_fieldname    EXPORTING      i_tabname   = 'VBAP'      i_fieldname = 'KWMENG'      i_sign      = '~'    IMPORTING      e_combined  = gv_source.*Add the where line  CALL METHOD ob_select->add_line    EXPORTING      i_line = gv_source.ENDFORM.                    " zf_build_select*&---------------------------------------------------------------------**&      Form  zf_build_from*&---------------------------------------------------------------------*FORM zf_build_from .  CREATE OBJECT ob_from.*Add opening bracket  CALL METHOD ob_from->add_opening_bracket    .  CLEAR gv_source.*Add the join condition.This can be made*fully dynamic as per your requirement  gv_source = 'VBAK AS VBAK INNER JOIN VBAP AS VBAP'.*Add the where line  CALL METHOD ob_from->add_line    EXPORTING      i_line = gv_source.  CLEAR gv_source.*Add the join condition.This can be made*fully dynamic as per your requirement  gv_source = 'ON VBAK~VBELN = VBAP~VBELN'.*Add the where line  CALL METHOD ob_from->add_line    EXPORTING      i_line = gv_source.*Add the closing bracket  CALL METHOD ob_from->add_closing_bracket    .ENDFORM.                    " zf_build_from*&---------------------------------------------------------------------**&      Form  zf_build_where*&---------------------------------------------------------------------*FORM zf_build_where .  DATA :  lv_field TYPE REF TO data,  lv_field_low TYPE REF TO data,  lv_field_high TYPE REF TO data.  CREATE OBJECT ob_where.*Add the field VBELN : Sales Document*Use this method if you want to assign a single value to a field*Set the value for VBELN : Sales Document Number*******************************************************************  CALL METHOD ob_where->add_field*    EXPORTING*      i_fieldnm  = 'VBAK~VBELN'*      i_operator = '='*      i_intlen   = 10*      i_datatp   = 'CHAR'*    IMPORTING*      e_r_field  = lv_field.*  CALL METHOD ob_where->set_value_for_field*    EXPORTING*      i_fieldnm = 'VBAK~VBELN'*      i_value   = '0000120020'.*********************************************************************Use this method if you want to assign a range of values*Set a range for the Sales Document number********************************************************************  CALL METHOD ob_where->add_field_between_2values    EXPORTING      i_fieldnm      = 'VBAK~VBELN'      i_intlen       = 10      i_datatp       = 'CHAR'    IMPORTING      e_r_field_low  = lv_field_low      e_r_field_high = lv_field_high.  CALL METHOD ob_where->set_2values_for_field    EXPORTING      i_fieldnm    = 'VBAK~VBELN'      i_value_low  = '0000120020'      i_value_high = '0000120067'.***********************************************************************Set the 'AND' Clause  CALL METHOD ob_where->add_and.*Add the field MATNR : Material  CALL METHOD ob_where->add_field    EXPORTING      i_fieldnm  = 'MATNR'      i_operator = '='      i_intlen   = 18      i_datatp   = 'CHAR'    IMPORTING      e_r_field  = lv_field.*Set the value for the Material field  CALL METHOD ob_where->set_value_for_field    EXPORTING      i_fieldnm = 'MATNR'      i_value   = '000000000050111000'.*Set the 'AND' Clause  CALL METHOD ob_where->add_and    .*Add the field VKORG  CALL METHOD ob_where->add_field    EXPORTING      i_fieldnm  = 'VKORG'      i_operator = '='      i_intlen   = 4      i_datatp   = 'CHAR'    IMPORTING      e_r_field  = lv_field.*Set the value for VKORG : Sales Organization  CALL METHOD ob_where->set_value_for_field    EXPORTING      i_fieldnm = 'VKORG'      i_value   = 'GMUS'.ENDFORM.                    " zf_build_where

Note:
Here I have hardcoded the values for sales order,sales organization and material for demonstration purposes.You can either change these values or modify the code as per your own needs.