topic Re: Any Code that Replaces Inner Join? in Application Development and Automation Discussions

Any Code that Replaces Inner Join?

former_member207873 — Wed, 21 Aug 2013 06:02:10 GMT

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.

Re: Any Code that Replaces Inner Join?

FredericGirod — Wed, 21 Aug 2013 06:09:14 GMT

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

Re: Any Code that Replaces Inner Join?

former_member207873 — Wed, 21 Aug 2013 06:13:13 GMT

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.

Re: Any Code that Replaces Inner Join?

Former Member — Wed, 21 Aug 2013 06:13:17 GMT

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

Re: Any Code that Replaces Inner Join?

FredericGirod — Wed, 21 Aug 2013 06:15:02 GMT

So just had the VBRP ~POSNR field

regards

Fred

Re: Any Code that Replaces Inner Join?

Former Member — Wed, 21 Aug 2013 06:26:50 GMT

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

Re: Any Code that Replaces Inner Join?

former_member201275 — Wed, 21 Aug 2013 06:36:48 GMT

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.

Re: Any Code that Replaces Inner Join?

Former Member — Wed, 21 Aug 2013 06:44:11 GMT

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.

Re: Any Code that Replaces Inner Join?

former_member209120 — Wed, 21 Aug 2013 06:49:11 GMT

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.....

Re: Any Code that Replaces Inner Join?

ThomasZloch — Wed, 21 Aug 2013 07:29:30 GMT

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

Thomas

Re: Any Code that Replaces Inner Join?

former_member207873 — Wed, 21 Aug 2013 07:44:48 GMT

Thread Closed.