how to sepearte five or more table joins into different select statement to retrieve same data.

[Former Member]

I have these tables VBUK, VBAP, VBUP, LIPS, LIKP that I used to retrieve data from tables in a report program.

I have to avoid join condition in order to improve performance.

The join condition in select statement is below.

   select LIKP~VBELN LIKP~LFART LIKP~ERDAT LIKP~ERZET LIKP~ERNAM LIKP~VKORG LIKP~KUNAG LIKP~KUNNR LIKP~VSTEL LIKP~WADAT_IST
           LIKP~WADAT LIPS~VTWEG LIPS~SPART LIPS~POSNR LIPS~PSTYV LIPS~MATNR LIPS~MATWA LIPS~WERKS LIPS~LFIMG LIPS~VRKME LIPS~LGORT
           LIPS~CHARG LIPS~SHKZG LIPS~VGBEL LIPS~VGPOS LIPS~VBELN VBUP~WBSTA VBUP~KOSTA VBUP~VBELN VBUP~POSNR VBAP~ERDAT VBAP~VBELN
           VBAP~POSNR VBAP~NETPR VBAP~WAERK VBAP~KZWI1 VBAP~VRKME VBAP~KMEIN VBAP~KWMENG VBAP~KZWI3 VBUK~CMGST VBUK~VBELN
    into CORRESPONDING FIELDS OF TABLE itab3
    from ( LIKP
           inner join LIPS
           on  LIPS~VBELN = LIKP~VBELN
           inner join VBUP
           on  VBUP~VBELN = LIPS~VBELN
           and VBUP~POSNR = LIPS~POSNR
           inner join VBAP
           on  VBAP~VBELN = LIPS~VGBEL
           and VBAP~POSNR = LIPS~VGPOS
           inner join VBUK
           on  VBUK~VBELN = LIKP~VBELN )
         where  LIKP~ERDAT in StDate
           and LIKP~VKORG = SaleOrg
           AND LIPS~WERKS = Plant1  .

How I can effectively split this code to retrieve same data.

Thank You.

[martin_roldan]

Hi,

You should do your first SELECT to LIKP joined to LIPS. Then you can SELECT the other tables using FOR ALL ENTRIES with the result of the first SELECT.

Martin

[nabheetscn]

Toby

Joon are anydau better than for all entries. Please search scn for the same. Secondly how did you know this is the select statement where issue is?

Nabheet

[Former Member]

Nabheet,

There are more than a million data to retrieve.

I have seen example for FOR ALL ENTRIES in two tables but not on 5 or more tables.

[nabheetscn]

Toby

Can ypu please explain us the background of this requirement

Nabheet

[Former Member]

Company1 has grown and policy do not allow monopoly. So company have to split. They sold a part of business to another company2. The new company2 need some historical data from Company1.

[nabheetscn]

Toby

There are various options. Joins are anyday better than for all entries. You can add package size to fetch data in chunks or you can schedule to run the report in backgroud.

What exactly business wants to do with this dat they want to archieve/report/send to warehousibg system

Nabheet

[Former Member]

As my guess they have do not have an idea about what they gonna do with the extracted data. Because they have not setup all the configurations for these data in SAP system. The data is from year 2007.

[nabheetscn]

Oops..I believe better approach will be to first understand the requirement from overall point of view and find the right solution

Nabheet

[Former Member]

Hi Mr. Toby tom,

I would suggest, please avoid the selects ( either inner join or for all entries )! better to use the below FM!

FM  BAPI_DELIVERY_GETLIST : You can pass the select parameters like below)

Pass the Parameters  ( ERDAT , VKORG, WERKS)

it will return the parameters : read the documentation

[nabheetscn]

Kiran very nice:)