Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Any Code that Replaces Inner Join?

former_member207873
Participant
0 Kudos

Hi Experts,

I have a select option field SO_FKDAT (DATE FIELD) in a selection screen. Based on the value of SO_FKDAT field I have to take the records from the VBRK table. Then with the VBELN field of VBRK I have to take value from VBRP table.

Here is the code I wrote for achieving the same and it is working perfectly.

SELECT VBRK~FKDAT VBRK~VBELN VBRK~FKART VBRK~KUNAG VBRK~KUNRG VBRK~VKORG VBRK~VTWEG VBRK~NETWR VBRP~VKBUR
   INTO CORRESPONDING FIELDS OF TABLE IT_FINOVR FROM VBRK JOIN VBRP ON ( VBRK~VBELN = VBRP~VBELN ) WHERE VBRK~FKDAT IN SO_FKDAT.

But I want to know whether there are any other coding styles to achieve the same. For All Entries In is not an option since it does not take duplicate entries.

Regards,

Marina.



10 REPLIES 10

FredericGirod
Active Contributor
0 Kudos

Hi Marina,

in your case inner join is a good solution

SELECT VBELN FKART KUNAG KUNRG ...

INTO IT_VBRK

FROM VBRK

WHERE KFDAT IN S_FKDAT.

CHECK NOT IT_VBRK[] IS INITIAL.

SELECT VBELN VKBUR ...

INTO IT_VBRP

FROM VBRP

FOR ALL ENTRIES IN IT_VBRK

WHERE VBELN EQ IT_VBRK-VBELN.

LOOP AT IT_VBRK.

READ IT_VBRP ...

ENDLOOP.

(you could improve performance of the LOOP)

regards

Fred

0 Kudos

Hi Fred,

Thanks for your solution. But I have tried with FOR ALL ENTRIES IN. But FAE does not give you duplicate values. I need to have duplicate values as well.

Regards,

Marina.

0 Kudos

So just had the VBRP ~POSNR field

regards

Fred

0 Kudos

Hi Marina,

When you post an FAE SAP does an internal delete adjacent duplicates to remove if any duplicates so you nedd to be sure to add the key fields of the resultant table in it.

So as recommended by Fred you can add posnr which will make it unique.

But as for for best solution your query already is a best performing as FAE with huge data will be slower than join and you will be reading the header increasing the processing time too.

JOIN is the best option in 1 : N scenario when you have key same,

Regards

Former Member
0 Kudos

Hi Marina,

The use of 'join' is a good option and i think that you can proceed with it(if "for all entries" is not useful for you).

Regards,

Shahir Mirza

former_member201275
Active Contributor
0 Kudos

Another thought might be to try LDB VFV? Sometimes I have managed to reduce processing time by using logical databases.

Just an idea, hope it helps.

Former Member
0 Kudos

Hi Marina,

Even if you use for all entries it will fetch all records provided you need to select all key fields.

Try this this will resolve your Issue.But still i think for VBRK & VBRP select using join is better option.

former_member209120
Active Contributor
0 Kudos

Hi Marina Debrova,

VBRK - Billing Document: Header Data

VBRP - Billing Document: Item Data

Key filed between VBRK & VBRP

VBELN - Billing Document

POSNR is item filed for VBELN - Billing Document

So try like this you will get as per your requirement

TABLES : vbrk.


TYPES : BEGIN OF ty_finovr,
         fkdat TYPE  vbrk-fkdat,
         vbeln TYPE  vbrk-vbeln,
         fkart TYPE  vbrk-fkart,
         kunag TYPE  vbrk-kunag,
         kunrg TYPE  vbrk-kunrg,
         vkorg TYPE  vbrk-vkorg,
         vtweg TYPE  vbrk-vtweg,
         netwr TYPE  vbrk-netwr,
         vkbur TYPE  vbrp-vkbur,
         posnr TYPE  vbrp-posnr,
         END OF ty_finovr.

DATA : it_finovr TYPE TABLE OF ty_finovr.

SELECT-OPTIONS : so_fkdat FOR  vbrk-fkdat.

SELECT vbrk~fkdat
        vbrk~vbeln
        vbrk~fkart
        vbrk~kunag
        vbrk~kunrg
        vbrk~vkorg
        vbrk~vtweg
        vbrk~netwr
        vbrp~vkbur
        vbrp~posnr
        INTO CORRESPONDING FIELDS OF TABLE it_finovr FROM vbrk JOIN vbrp ON ( vbrk~vbeln = vbrp~vbeln )
        WHERE vbrk~fkdat IN so_fkdat.
 
BREAK-POINT.

inner join is better ....So please follow as Fedric suggestion.....


ThomasZloch
Active Contributor
0 Kudos

You said it works perfectly. Why look for second best alternatives?


Thomas

former_member207873
Participant
0 Kudos

Thread Closed.