Both ABAP language components, “Select-Options” and “dynamic WHERE statement” are very useful and powerful features. With Select-Options you can create a very complex selection and use it straightforward using IN operator in WHERE condition of SELECT statement. The WHERE condition can be dynamic and stored in the internal table. Since SAP Basis 6.40 the dynamic WHERE condition can include IN operator and selection table. It works fine when the Select-Options selection table is available for the WHERE clause. If it’s not, you will get the dump SAPSQL_WHERE_ILLEGAL_VALUE. Such a situation occurs for example, when the SELECT is placed in a function, which is called per RFC and the content of WHERE condition is passed through the parameters of a function. In such case you do what Open SQL does for you: translate IN operator and create from selection table the classical condition.
I’ve wrote a small program, which does this translation.
Below the output of this program and its code.
At the end of the program two SELECT instructions are started and compared,
first with IN operator and the second with classic condition.
REPORT zs_where.
DATA:
gv_dummy_bname TYPE usr01-bname.
SELECT-OPTIONS:
so_bname FOR gv_dummy_bname.
*&---------------------------------------------------------------------*
*& START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
DATA:
lt_options TYPE esh_t_co_rfcrt_options,
ls_options TYPE rfc_db_opt,
lt_selopt TYPE ert_range_option,
ls_selopt TYPE ers_range_option,
lt_tab1 TYPE TABLE OF usr01,
lt_tab2 TYPE TABLE OF usr01.
* Show the SELECT-OPTION table
WRITE: / 'SELECT-OPTION:'.
LOOP AT so_bname.
WRITE: / so_bname-sign,
so_bname-option,
so_bname-low,
so_bname-high.
ENDLOOP.
SKIP.
* Create the dynamic WHERE statement
LOOP AT so_bname.
MOVE-CORRESPONDING so_bname TO ls_selopt.
APPEND ls_selopt TO lt_selopt.
ENDLOOP.
PERFORM selopt_to_where USING 'BNAME' lt_selopt CHANGING lt_options.
* Show the WHERE statement
WRITE: / 'WHERE statement:'.
LOOP AT lt_options INTO ls_options.
WRITE: / ls_options.
ENDLOOP.
SKIP.
* Load data with created dynamic WHERE statement
SELECT *
APPENDING TABLE lt_tab1
FROM usr01
WHERE bname IN so_bname.
* Load data using IN operator
SELECT *
APPENDING TABLE lt_tab2
FROM usr01
WHERE (lt_options).
* Compare the results of both loads
IF lt_tab1 = lt_tab2.
WRITE: / 'Test OK, the results of both SELECT''s are equal'.
ELSE.
WRITE: / 'Error, the results of both SELECT''s are not equal'.
ENDIF.
*&---------------------------------------------------------------------*
*& Form selopt_to_where
*&---------------------------------------------------------------------*
FORM selopt_to_where USING pi_field TYPE string
value(pt_selopt) TYPE ert_range_option
CHANGING pt_options TYPE esh_t_co_rfcrt_options.
DATA:
ls_selopt TYPE ers_range_option,
ls_options TYPE rfc_db_opt,
lv_first_sign TYPE i,
lv_first_pos TYPE i,
lv_and TYPE string,
lv_not TYPE string,
lv_or TYPE string,
lv_oper TYPE string.
SORT pt_selopt BY sign DESCENDING.
lv_first_sign = 1.
LOOP AT pt_selopt INTO ls_selopt.
AT FIRST.
IF lines( pt_options ) = 0.
ls_selopt = '('.
ELSE.
ls_selopt = 'AND ('.
ENDIF.
APPEND ls_selopt TO pt_options.
ENDAT.
AT NEW sign.
IF lv_first_sign = 1.
lv_and = ''.
lv_first_sign = 0.
ELSE.
lv_and = 'AND'.
ENDIF.
IF ls_selopt-sign = 'I'.
lv_not = ''.
ELSE.
lv_not = 'NOT'.
ENDIF.
ls_options = '#AND #NOT ('.
REPLACE SUBSTRING '#AND' IN ls_options WITH lv_and.
REPLACE SUBSTRING '#NOT' IN ls_options WITH lv_not.
CONDENSE ls_options.
APPEND ls_options TO pt_options.
lv_first_pos = 1.
ENDAT.
* for every row
IF lv_first_pos = 1.
lv_or = ''.
lv_first_pos = 0.
ELSE.
lv_or = 'OR'.
ENDIF.
CASE ls_selopt-option.
WHEN 'EQ'
OR 'NE'
OR 'GE'
OR 'GT'
OR 'LE'
OR 'LT'.
PERFORM operator_map USING ls_selopt-option CHANGING lv_oper.
ls_options = '#OR #FLD #OP1 ''#VA1'''.
REPLACE SUBSTRING '#OP1' IN ls_options WITH lv_oper.
REPLACE SUBSTRING '#VA1' IN ls_options WITH ls_selopt-low.
WHEN 'CP'
OR 'NP'.
ls_options = '#OR #FLD #NOT #OP1 ''#VA1'''.
IF ls_selopt-option = 'CP'.
lv_not = ''.
ELSE.
lv_not = 'NOT'.
ENDIF.
REPLACE SUBSTRING '#NOT' IN ls_options WITH lv_not.
REPLACE SUBSTRING '#OP1' IN ls_options WITH 'LIKE'.
REPLACE ALL OCCURRENCES OF SUBSTRING '*' IN ls_selopt-low WITH '%'.
REPLACE ALL OCCURRENCES OF SUBSTRING '+' IN ls_selopt-low WITH '_'.
REPLACE SUBSTRING '#VA1' IN ls_options WITH ls_selopt-low.
WHEN 'BT'
OR 'NB'.
IF ls_selopt-option = 'BT'.
lv_not = ''.
ELSE.
lv_not = 'NOT'.
ENDIF.
ls_options = '#OR #FLD #NOT #OP1 ''#VA1'' #OP2 ''#VA2'''.
REPLACE SUBSTRING '#NOT' IN ls_options WITH lv_not.
REPLACE SUBSTRING '#OP1' IN ls_options WITH 'BETWEEN'.
REPLACE SUBSTRING '#VA1' IN ls_options WITH ls_selopt-low.
REPLACE SUBSTRING '#OP2' IN ls_options WITH 'AND'.
REPLACE SUBSTRING '#VA2' IN ls_options WITH ls_selopt-high.
ENDCASE.
REPLACE SUBSTRING '#OR' IN ls_options WITH lv_or.
REPLACE SUBSTRING '#FLD' IN ls_options WITH pi_field.
CONDENSE ls_options.
APPEND ls_options TO pt_options.
AT END OF sign.
ls_options = ')'.
APPEND ls_options TO pt_options.
ENDAT.
AT LAST.
ls_options = ')'.
APPEND ls_options TO pt_options.
ENDAT.
ENDLOOP.
ENDFORM. "selopt_to_where
*&---------------------------------------------------------------------*
*& Form operator_map
*&---------------------------------------------------------------------*
FORM operator_map USING pi_oper
CHANGING po_oper.
CASE pi_oper.
WHEN 'EQ'.
po_oper = '='.
WHEN 'NE'.
po_oper = '<>'.
WHEN 'GT'.
po_oper = '>'.
WHEN 'LT'.
po_oper = '<'.
WHEN 'GE'.
po_oper = '>='.
WHEN 'LE'.
po_oper = '<='.
ENDCASE.
ENDFORM. "operator_mapSee other related notes on my website:
- Commit Work in SELECT statement
- Information about amount of read records returned by SELECT, SELECT SINGLE, SELECT COUNT, etc.
- Package processing of mass data with database commit and SELECT statement
- SQL inner join vs. join of internal tables
- Split ABAP string to internal table retaining whole words
- Package processing of mass data with COMMIT WORK and SELECT statement