Select-Options in dynamic WHERE condition called per RFC
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.
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_map
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_map
See other related notes in my infodepot:
Full list of examples in my infodepot
If you have a question, have found an error or just want to contact me, please use this form.
If you have a question, have found an error or just want to contact me, please use this form.
Copyright (C) 2011 http://www.kerum.pl/infodepot/
Disclaimer: I am not affiliated or related to any division or subsidiary of SAP AG.
Trademarks or registered trademarks of any products or companies referred to on this site belong to those companies.
Anyone using the given solutions, is doing it under his/her own responsibility and at own risk.