Motivation
In SAP BW, usually there is no easy way of performing set operations (union, intersection, outer joins etc.) of two queries. This is usually a manual activity. Basically, post processing using two query results is difficult. Infosets can be used in some scenarios but these need additional modelling steps and not as flexible. This blog illustrates how we can fetch values from one query and then exclude these selected values from another query.
h2. Business Example
Using technical content, you want to find out all infocubes that exists in the system but with no data being fetched from them over the last two years (via queries say).
Let X = {A1, A2, .. AN} be a query that delivers all the cubes in the system.
Let Y = {A1, A2, A3} be the query that delivers all the cubes in use over the last two years i.e. some data was fetched from them.
You want to find Z = X - Y = {A4, A5 .. AN}.
h2. Steps
*1) In query X, create a customer exit variable of type Selection option on characteristic A.</p><p> </p><p>2)* Write the customer exit coding in FUNCTION EXIT_SAPLRRS0_001 include ZXRSRU01. We calculate the variable values set at i_step=1. (How customer exit variables are processed is out of scope of this blog)
3) Code sample
Initialization of the request object (Every query execution is represented in BW via a request object).
DATA:
l_r_request TYPE REF TO cl_rsr_request,
l_s_compkey TYPE rszcompkey.
*** Initialization with query Y to be executed from within the ABAP code ***
l_s_compkey-objvers = rs_c_objvers-active.
l_s_compkey-infocube = 'INFOCUBE'.
l_s_compkey-compid = 'QUERY_Y'. *** executing query Y
l_s_compkey-comptype = rzd1_c_comptp-report.
4) Get the internal query id for QUERY_Y
* Get the compkey
CALL FUNCTION 'RRI_REPDIR_READ'
CHANGING
c_s_repkey = l_s_compkey
EXCEPTIONS
entry_not_found = 1.
IF sy-subrc <> 0.
MESSAGE s792(brain) WITH l_s_compkey-compid.
EXIT.
ENDIF.
5) Create the Request object
CREATE OBJECT l_r_request
EXPORTING
i_genuniid = l_s_compkey-genuniid
EXCEPTIONS
OTHERS = 1.
* 6)* Call the query Y from within ABAP code
* get the query definition
CALL METHOD l_r_request->get_initial_state
IMPORTING
e_t_dim = l_t_dim
e_t_mem = l_t_mem
e_t_cel = l_t_cel
e_t_atr = l_t_atr
e_t_con = l_t_con
e_t_fac = l_t_fac
e_t_prptys = l_t_prptys
EXCEPTIONS
x_message = 8
OTHERS = 1.
* Set the request
CALL METHOD l_r_request->set_request
EXPORTING
i_t_dim = l_t_dim
i_t_mem = l_t_mem
i_t_cel = l_t_cel
i_t_atr = l_t_atr
i_t_con = l_t_con
i_t_fac = l_t_fac
i_t_prptys = l_t_prptys
EXCEPTIONS
no_processing = 1
x_message = 8.
* read data
CALL METHOD l_r_request->read_data
IMPORTING
e_warnings = l_warnings
EXCEPTIONS
no_processing = 1
x_message = 8.
close the request</p><p>l_r_request->p_r_olap->FREE( ).</p><p> </p><p>7) After fetching the results, assign the result set elements {A1, A2, A3} as exclude 'E' to the exit variable.</p><p> Get the text table from the output handle of the requestl_s_range-sign = 'E'. *** Excluding
l_s_range-opt = 'EQ'.
append l_s_range to e_t_range.
endloop.
......
e_t_range contains the variable values which will finally be submitted to OLAP to fetch the results of query X excluding values of Y i.e. X - Y.
Thus, in this way you can perform other set operations simply in the customer exit coding itself.
* P.S.* For more details on executing BW queries from within ABAP, please refer: http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/43db5ee1-0701-0010-2d90-c3b9...</p>
