topic Re: FAE equivalent query using join( FAE query Convert to JOIN ) in Application Development and Automation Discussions

FAE equivalent query using join( FAE query Convert to JOIN )

Former Member — Tue, 22 Mar 2016 10:51:31 GMT

Hi All experts,

I am new to SAP and i have program for improving the performance. i want to write Query using join which reproduce same result in it_mkpf which is currently produced using "For all entry". I want to remove "For all entry" without changing end result.

SELECT objid arbpl FROM crhd INTO TABLE ist_crhd
WHERE werks = p_plant
AND arbpl IN r_arbpl.

SELECT budat arbid wablnr FROM afru INTO TABLE ist_afru
FOR ALL ENTRIES IN ist_crhd
WHERE budat BETWEEN l_date1 AND p_date-high
AND   arbid = ist_crhd-objid
AND   werks = p_plant

SELECT mblnr mjahr budat cputm FROM mkpf INTO TABLE it_mkpf "09.07.10
FOR ALL ENTRIES IN ist_afru
WHERE ( budat BETWEEN p_date-low AND p_date-high )
AND   mblnr = ist_afru-wablnr

Thanks in advance

Re: FAE equivalent query using join( FAE query Convert to JOIN )

former_member787072 — Tue, 22 Mar 2016 13:30:07 GMT

Hello Chintan Choksi.

You can only join two or more tables if and only if they have same key in common.You can join up to 20 tables using inner/outer joins but more than 4-5 joins will degrade the performance.

In you case tables AFRU (Order confirmations) and MKPF(Material Doc header) cannot be joined if they donot have any key in common- i think no possibility here.

One more thing is that as you are new to abap, Please check the internal table for intial condition before using it in for all entries like...

IF ist_crhd IS NOT INITIAL.

SELECT budat arbid wablnr FROM afru INTO TABLE ist_afru

FOR ALL ENTRIES IN ist_crhd

WHERE budat BETWEEN l_date1 AND p_date-high

AND arbid = ist_crhd-objid

AND werks = p_plant

ENDIF,

If you dont not check for this condition before for all entries, it will try to bring all the records from AFRU for all the values of ARBID, Which eventullay decrease the performance.

Hope this helps.

Regards,

Prasad CH.

Re: FAE equivalent query using join( FAE query Convert to JOIN )

matt — Tue, 22 Mar 2016 14:06:01 GMT



You can only join two or more tables if and only if they have same key in common.

Incorrect. It can be any common fields. Obviously if there is no good key performance may be terrible. But it will work.


You can join up to 20 tables using inner/outer joins

There is no limit as far as I am aware. (Unless you're able to cite a source for this information...?)


 more than 4-5 joins will degrade the performance.

Incorrect.



In you case tables AFRU (Order confirmations) and MKPF(Material Doc header) cannot be joined if they donot have any key in common- i think no possibility here.

Incorrect. See my first comment above.

For the OP - something like this.

SELECT crhd~objid crhd~arbpl

afru~budat afru~arbid afru~wablnr

mkpf~mblnr mkpf~mjahr mkpf~budat mkpf~cputm

INTO TABLE some_table

FROM crhd

INNER JOIN afru

ON afru~arbid EQ crhd~objid

INNER JOIN mkpf

ON mkpf~mblnr EQ afru~wablnr

WHERE crhd~werks EQ p_plant
AND crhd~arbpl IN r_arbpl

AND afru~budat BETWEEN l_date1 AND p_date-high

AND afru~werks EQ p_plant
AND mkpf~budat BETWEEN p_date-low AND p_date-high ).

Re: FAE equivalent query using join( FAE query Convert to JOIN )

SuhaSaha — Tue, 22 Mar 2016 14:32:33 GMT

Hi Matt,

As per SAP documentation


The maximum number of individual joins in a join expression is 24. A maximum of 25 transparent database tables or views can be joined using these joins.

Source - ABAP Keyword Documentation

Re: FAE equivalent query using join( FAE query Convert to JOIN )

matt — Tue, 22 Mar 2016 15:13:21 GMT

So not 20 then!

Re: FAE equivalent query using join( FAE query Convert to JOIN )

SuhaSaha — Tue, 22 Mar 2016 15:26:16 GMT

And in any case > 5. I, personally, start feeling dizzy after joining 3 tables

Re: FAE equivalent query using join( FAE query Convert to JOIN )

matt — Tue, 22 Mar 2016 15:48:14 GMT

But technically, there's no reason not to join up to 25 tables. I think the most I've done is 5 - that was an extractor for HR Appraisal documents. It was all quite straightforward - one table with the key information for the other 4

i.e.

FROM hrhap AS hrhap

INNER JOIN hrhap_basic AS basic

ON basic~plan_version EQ hrhap~plan_version

AND basic~appraisal_id EQ hrhap~appraisal_id

INNER JOIN hrhap_appee AS appee

ON appee~plan_version EQ hrhap~plan_version

AND appee~appraisal_id EQ hrhap~appraisal_id

INNER JOIN hrhap_apper AS apper

ON apper~plan_version EQ hrhap~plan_version

AND apper~appraisal_id EQ hrhap~appraisal_id

Re: FAE equivalent query using join( FAE query Convert to JOIN )

Jelena_Perfiljeva — Tue, 22 Mar 2016 21:18:47 GMT

Phew, at least on one area my skills are superior:


FROM vbup JOIN lips ON vbup~vbeln = lips~vbeln AND
                       vbup~posnr = lips~posnr
          JOIN likp ON lips~vbeln = likp~vbeln
          JOIN vbap ON lips~vgbel = vbap~vbeln AND
                       lips~vgpos = vbap~posnr
          JOIN vbak ON vbak~vbeln = vbap~vbeln
          JOIN kna1 ON vbak~kunnr = kna1~kunnr

Would've added VBKD too if it wasn't for the stupid design with mixed use of POSNR.

Re: FAE equivalent query using join( FAE query Convert to JOIN )

Colleen — Wed, 23 Mar 2016 00:53:09 GMT

almost a game of 'my code is more complex than yours'?

Re: FAE equivalent query using join( FAE query Convert to JOIN )

matt — Wed, 23 Mar 2016 08:29:28 GMT

Would not

FROM vbup JOIN lips ON vbup~vbeln = lips~vbeln AND

vbup~posnr = lips~posnr

JOIN likp ON lips~vbeln = likp~vbeln

JOIN vbap ON lips~vgbel = vbap~vbeln AND

lips~vgpos = vbap~posnr

JOIN vbak ON vbap~vbeln = vbak~vbeln

JOIN kna1 ON vbak~kunnr = kna1~kunnr

be more consistent?

Re: FAE equivalent query using join( FAE query Convert to JOIN )

Jelena_Perfiljeva — Wed, 23 Mar 2016 13:24:37 GMT

Does it make any difference from the execution standpoint?

Re: FAE equivalent query using join( FAE query Convert to JOIN )

ThomasZloch — Wed, 23 Mar 2016 13:37:55 GMT

Rather proofs of concept

Thomas

Re: FAE equivalent query using join( FAE query Convert to JOIN )

Jelena_Perfiljeva — Wed, 23 Mar 2016 13:48:26 GMT

Oh boy... Thomas, you are in a totally different league here.

Re: FAE equivalent query using join( FAE query Convert to JOIN )

ThomasZloch — Wed, 23 Mar 2016 14:19:01 GMT

No no, this was growing over a long time, you are seeing the final result.

Admittedly, it can be challenging to find the missing link in case the returned data doesn't meet expectations, but then we can always split into FAE's, right?

Thomas

Re: FAE equivalent query using join( FAE query Convert to JOIN )

matt — Wed, 23 Mar 2016 14:47:19 GMT

No - just maintenance... 🙂

Re: FAE equivalent query using join( FAE query Convert to JOIN )

Sandra_Rossi — Wed, 23 Mar 2016 21:05:35 GMT

If you need to extend your 22-tables join with 4 other tables, what will you do? LOL

Re: FAE equivalent query using join( FAE query Convert to JOIN )

ThomasZloch — Thu, 31 Mar 2016 07:48:36 GMT

I'll write to Horst Keller if they can raise the limit. 25 sounds arbitrary to me, why not 27 or 35?