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

[Former Member]

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

[Former Member]

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

[former_member787072]

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.

[matt]

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

[SuhaSaha]

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

[matt]

So not 20 then!

[SuhaSaha]

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

[matt]

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

[Jelena_Perfiljeva]

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.

[Colleen]

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

[matt]

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?

[Jelena_Perfiljeva]

Does it make any difference from the execution standpoint?

[ThomasZloch]

Rather proofs of concept

Thomas

[Jelena_Perfiljeva]

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

[ThomasZloch]

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

[matt]

No - just maintenance... 🙂

[Sandra_Rossi]

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

[ThomasZloch]

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