One generic extractor with multiple cursors

One generic extractor with multiple cursors         

Applies to:

SAP BW 7.x system, for more information, visit the Business Intelligence homepagehttps://help.sap.com/saphelp_nw73ehp1/helpdata/en/b2/e50138fede083de10000009b38f8cf/frameset.htm

Summary

This Document describes how to use multiple cursors in a single generic extractor.

Author Bio

Lakshminarasimhan Narasimhamurthy is BW certified and ABAP certified consultant and worked on multiple implementation, development and support project’s within India and outside of India. He has worked for fortune 500 clients like Nestle, Warner Bros, GCC, General Electric etc. He is strong in BW, BW related ABAP, HANA modeling, BW-HANA modeling, BODS and BO/BI tools like WEBI, Design Studio, IDT and Lumira.

Details

There might be requirement from Business to combine data from 2 or more table. The tables might have no relationship with each other but contain business related data. In this case we cannot do a “inner join”. The easy way is to create 2 separate DataSource’s over these tables. But here in this document I am explaining how to have 2 cursors and fetch data from both of these tables and combine them in a single generic extractor.

I am giving an example say there is 15 steps to complete a task. 

Step 1 to 11 is maintained in one table ZA_TASK1TO11.

Step 12 to 15 is maintained in second table ZA_TASK12TO15.

The steps are sequential, only if 1 is completed the step 2 will begin and only when step 2 is completed step 3 will begin.

You can assume this as a workflow. Now Business wants to know upto what step the task is complete. Unfortunately there is no task id relationship,

the primary key of both the table  is the concatenation of (taskno_stepnumber) and the primary key is non-character.

So if the generic extractor based on FM RSAX_BIW_GET_DATA_SIMPLE.

I have created 2 cursors.

STATICS: S_CURSOR            TYPE CURSOR,

        C_CURSOR            TYPE CURSOR.

      OPEN CURSOR WITH HOLD S_CURSOR FOR

Select task_num comp_indicator from ZA_TASK1TO11.

      OPEN CURSOR WITH HOLD C_CURSOR FOR

Select task_num comp_indicator from ZB_TASK12TO15.

Initially the cursors will be zero and once the “open cursor” statement is executed, the S_CURSOR will have the value 1 and C_CURSOR will have the value 2 etc.

Suppose if at all, you have another cursor and you open the cursor then it will have value 3 and so on.

Now the trick lies in waiting for the both of these cursors to close before raising the exception

RAISE NO_MORE_DATA.

Usually we will have only one cursor and when the fetch fails, then the cursor is closed and the exception “RAISE NO_MORE_DATA” is raised, to close the data extraction. 

In our case we have 2 cursors, so if the data fetch for cursor one fails, then it need to halt for the second cursor and wait until the fetch fails for the second cursor too.

After both the data fetch fails then the exception RAISE NO_MORE_DATA needs to be raised. This is vice versa too, if the data fetch for cursor two fails, then it need to halt for the first cursor and wait until the fetch fails for the first cursor too. After both the data fetch fails then the exception RAISE NO_MORE_DATA needs to be raised.

When the cursor is closed then it will have the value 0 in it.As the extractor will be called multiple times during the data fetch, consider the following example, during the first call the cursors are opened and during the second call the data is fetched from both of the cursors and appended to the table E_T_DATA & in the third call the cursor one fails and hence we close the cursor to make it zero and we check for the second cursor if it is closed, but still second cursor has data and hence it has the value 2, in such case the extractor is called for the fourth time. In the fourth call, the first cursor is checked, only if it is not 0(not closed) then we try to fetch the data, as in our case the first cursor is already closed and hence we go to the next block.

The second cursor(C_CURSOR) is checked if it is 0, the condition will fail as it is having value 2 we go inside the if block, now  inside the if condition the fetch fails and we close cursor(C_CURSOR), so that we make it zero. Now we check to see if the first cursor is already closed, in our case it is closed already.

So the exception RAISE NO_MORE_DATA is triggered.

IF S_CURSOR NE 0. “ To check if the cursor is already closed
FETCH NEXT CURSOR S_CURSOR APPENDING CORRESPONDING FIELDS OF TABLE E_T_DATA
PACKAGE SIZE S_S_IF–MAXSIZE.

   IF SY–SUBRC <> 0.
     CLOSE CURSOR S_CURSOR.
    

       IF C_CURSOR = 0.”Checks the second cursor if it is closed

         RAISE NO_MORE_DATA.
        ENDIF.

   ENDIF.

ENDIF.

IF C_CURSOR NE 0.

FETCH NEXT CURSOR C_CURSOR APPENDING CORRESPONDING FIELDS OF TABLE E_T_DATA
PACKAGE SIZE S_S_IF–MAXSIZE.

IF SY–SUBRC <> 0.
   CLOSE CURSOR C_CURSOR. ”Checks for first cursor if it is closed
I

            IF  S_CURSOR = 0.
        RAISE NO_MORE_DATA.
      ENDIF.
ENDIF.

ENDIF.

This way the extraction will stop only when both of cursor fail to fetch data and we will have combined data from both of the tables.