Question Re: How to get data more than 2 tables by use of RSAX_BIW_GET_DATA_SIMPLE in Technology Q&A

How to get data more than 2 tables by use of RSAX_BIW_GET_DATA_SIMPLE

pavneet_rana — Mon, 20 Dec 2010 11:09:22 GMT

Hi to all,

I need to create generic data source based on Function Module for transactional data which need to extract data from 7 tables.

I knew that standard Function Module RSAX_BIW_GET_DATA_SIMPLE can be used to create Custom Function Module.

But I have searched the SDN and found example related to RSAX_BIW_GET_DATA_SIMPLE but there only data from 2 table is used.

Is there any PDF, DOC file, or Blog which have demonstrated how to get data from more than 2 tables by using Function Module RSAX_BIW_GET_DATA_SIMPLE.

I shall be thankful to you for this.

Regards

Pavneet Rana

Re: How to get data more than 2 tables by use of RSAX_BIW_GET_DATA_SIMPLE

Former Member — Mon, 20 Dec 2010 11:27:54 GMT

Hi,

Its the similar way for multiple tables.

See below sample code starting from open cursor statement.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT qmnum

equnr

FROM viqmel

WHERE qmart IN l_r_qmart AND

( erdat IN l_r_erdat OR

aedat IN l_r_erdat ).

End of First Data package

ENDIF. "First data package ?

Fetch records into interface table.
named E_T_'Name of extract structure'.

FETCH NEXT CURSOR s_cursor

APPENDING CORRESPONDING FIELDS

OF TABLE it_viqmel this is the first internal table where data gets loaded. Based on the entries in this table, data would be fetched from other database tables

PACKAGE SIZE s_s_if-maxsize.

if records not selected raise exceptions as NO MORE DATA.
then close the currently opened cursor

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor.

RAISE no_more_data.

ENDIF.

SELECT qmnum manum mnkat mngrp

INTO CORRESPONDING FIELDS OF TABLE it_qmma

FROM qmma FOR ALL ENTRIES IN it_viqmel

WHERE qmnum EQ it_viqmel-qmnum

AND kzloesch EQ space.

This way you can collect teh data from multiple database tables, and then later you can put them into final internal table which has structure same as e_t_data. And finally move the data to e_t_data.

Edited by: Rahul K Rai on Dec 20, 2010 12:28 PM

Re: How to get data more than 2 tables by use of RSAX_BIW_GET_DATA_SIMPLE

pavneet_rana — Mon, 20 Dec 2010 12:29:59 GMT

Thanks for reply,

Please can you Demonstrate with some code example.

So i can better understand it.

I shall be thankful to you for this.

Regards

Pavneet Rana

Re: How to get data more than 2 tables by use of RSAX_BIW_GET_DATA_SIMPLE

Former Member — Mon, 20 Dec 2010 12:59:58 GMT

Hi,

The above piece was part of a code implemented in our system.

Only thing not mentioned in it was how would you collect the data into final table.

So, here it is as well.

LOOP AT it_viqmel.

MOVE-CORRESPONDING it_viqmel TO it_final. this statement would collect the data from similar fields to final table

APPEND it_final.

CLEAR it_final.

ENDLOOP.

LOOP AT it_final.

READ TABLE it_internaltable WITH KEY KEY1 = it_final-KEY.

IF sy-subrc EQ 0.

it_final-field1 = it_iflot-field2. here you are moving the required field from some other internal table into this final table

MODIFY it_final TRANSPORTING field1.

ENDIF.

endloop.

Similarly, you would move the remaining fields to final internal table.

Then to move into e_t_data the below

LOOP AT it_final.

MOVE-CORRESPONDING it_final TO e_t_data.

APPEND e_t_data.

CLEAR : it_final,e_t_data.

ENDLOOP.

Re: How to get data more than 2 tables by use of RSAX_BIW_GET_DATA_SIMPLE

pavneet_rana — Tue, 21 Dec 2010 05:34:58 GMT

Thanks for reply,

I am demonstrating an example, where I have used 3 tables with these fields.

SFLIGHT = CARRID CONNID FLDATE PRICE

SPFLI = CITYFROM CITYTO

SBOOK = BOOKID CUSTOMID PASSNAME

And I have created extract structure ZEXTRACT_ST_5

Of following fields CARRID, CONNID, FLDATE, PRICE, CITYFROM, CITYTO, BOOKID, CUSTOMID, PASSNAME

I am trying to get this entire field in E_T_DATA.

I have written a code, but still not getting result.

TABLES: SFLIGHT, SPFLI, SBOOK.

DATA: IT_FINAL TYPE TABLE OF ZEXTRACT_ST_5.

DATA: WA_FINAL LIKE LINE OF IT_FINAL.

Auxiliary Selection criteria structure

DATA: L_S_SELECT TYPE SRSC_S_SELECT.

Maximum number of lines for DB table

STATICS: S_S_IF TYPE SRSC_S_IF_SIMPLE,

counter

S_COUNTER_DATAPAKID LIKE SY-TABIX,

cursor

S_CURSOR TYPE CURSOR.

Select ranges

RANGES: L_R_CARRID FOR SFLIGHT-CARRID.

L_R_CONNID FOR SFLIGHT-CONNID.

Initialization mode (first call by SAPI) or data transfer mode
(following calls) ?

IF I_INITFLAG = SBIWA_C_FLAG_ON.

************************************************************************

Initialization: check input parameters
buffer input parameters
prepare data selection

************************************************************************

Check DataSource validity

CASE I_DSOURCE.

WHEN 'ZNVBAK5D'. u201C DATASOURCE NAME u201C

WHEN OTHERS.

IF 1 = 2. MESSAGE E009(R3). ENDIF.

this is a typical log call. Please write every error message like this

LOG_WRITE 'E' "message type

'R3' "message class

'009' "message number

I_DSOURCE "message variable 1

' '. "message variable 2

RAISE ERROR_PASSED_TO_MESS_HANDLER.

ENDCASE.

APPEND LINES OF I_T_SELECT TO S_S_IF-T_SELECT.

Fill parameter buffer for data extraction calls

S_S_IF-REQUNR = I_REQUNR.

S_S_IF-DSOURCE = I_DSOURCE.

S_S_IF-MAXSIZE = I_MAXSIZE.

Fill field list table for an optimized select statement
(in case that there is no 1:1 relation between InfoSource fields
and database table fields this may be far from beeing trivial)

APPEND LINES OF I_T_FIELDS TO S_S_IF-T_FIELDS.

ELSE. "Initialization mode or data extraction ?

************************************************************************

Data transfer: First Call OPEN CURSOR + FETCH
Following Calls FETCH only

************************************************************************

First data package -> OPEN CURSOR

IF S_COUNTER_DATAPAKID = 0.

LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT WHERE FIELDNM = 'CARRID'.

MOVE-CORRESPONDING L_S_SELECT TO L_R_CARRID .

APPEND L_R_CARRID.

ENDLOOP.

OPEN CURSOR WITH HOLD S_CURSOR FOR

SELECT SFLIGHT_{CARRID SFLIGHT}CONNID SFLIGHT_{FLDATE SFLIGHT}PRICE

SPFLI_{CITYFROM SPFLI}CITYTO

FROM SFLIGHT INNER JOIN SPFLI ON SFLIGHT_{CARRID = SPFLI}CARRID

WHERE SFLIGHT~CARRID IN L_R_CARRID .

ENDIF. "First data package ?

Fetch records into interface table.
named E_T_'Name of extract structure'.

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.

RAISE NO_MORE_DATA.

ENDIF.

DATA: IT1 TYPE STANDARD TABLE OF SBOOK INITIAL SIZE 0.

DATA: WA1 LIKE LINE OF IT1.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE IT1

FROM SBOOK FOR ALL ENTRIES IN E_T_DATA WHERE CARRID = E_T_DATA-CARRID

lOOP AT E_T_DATA.

MOVE-CORRESPONDING E_T_DATA TO IT_FINAL.

APPEND IT_FINAL.

ENDLOOP.

LOOP AT IT_FINAL INTO WA_FINAL.

READ TABLE IT1 INTO WA1 WITH KEY CARRID = WA_FINAL-CARRID .

IF SY-SUBRC = 0.

WA_FINAL-BOOKID = WA1-BOOKID .

WA_FINAL-CUSTOMID = WA1-CUSTOMID .

WA_FINAL-PASSNAME = WA1-PASSNAME .

MODIFY IT_FINAL FROM WA_FINAL TRANSPORTING BOOKID CUSTOMID PASSNAME.

ENDIF.

ENDLOOP.

LOOP AT IT_FINAL .

MOVE-CORRESPONDING IT_FINAL TO E_T_DATA.

APPEND E_T_DATA.

CLEAR : IT_FINAL,E_T_DATA.

ENDLOOP.

S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.

ENDIF. "Initialization mode or data extraction ?

ENDFUNCTION.

Please can you help me with this.

I shall be thankfull to you for this .

Regards

Pavneet Rana

Edited by: pavneet rana on Dec 21, 2010 7:27 AM

Re: How to get data more than 2 tables by use of RSAX_BIW_GET_DATA_SIMPLE

Former Member — Tue, 21 Dec 2010 07:29:23 GMT

there are many reasons why you don't have data.

first of all your keys when selecting/reading data are not correct. spfli is your main table. sflight is linked to this table via carrid and connid. then sbook is linked to sflight by carrid/connid/fldate.

then in your code you do not do what was suggested to you by the member in the first reply.

you have two option, depending what you want to see :

if you only want to see flights that have an entry in all three tables, you can open the cursor, select the data on a big innner join for all three tables, fetch cursor to put the data in e_t_data.

this is option one;

option two: you want to see all flights regardless of the fact that they have an entry in on of the other tables

create an internal table (itab1) with all the fields that you need to select from spfli (in this sequence)

open the curesor, select data from spfli, close cursor and put the data in itab1 (not e_t-data as in your example).

then :

select necessary fields from sflight for all entries in itab1 in itab2

select necessary fields from sbook for all entries in itab1 in itab3

loop over itab1

read table itab 2 with key carrid connid

read table itab 3 wtih key carred conned and itab2-fldate.

move corresponding ita1 to e_t_data

move corresponding ita2 to e_t_data

move corresponding ita3 to e_t_data

collect e_t_data.

this should be clear enough. if you still have an issue with the exact coding, i would suggest to ask help to a collaegue that knows abap.