cancel
Showing results for 
Search instead for 
Did you mean: 

Partner function determination HANA for VBAK

Former Member
0 Kudos

Hi,

I have a requirement where I need to retrieve a single partner function on sales order header level (SELECT SINGLE in ABAP)

Therefore I have created the following sql script calculation view:

LT_VBRK = SELECT MANDT, VBELN

FROM "SLT_PRD"."VBRK"

WHERE VBELN = :P_VBELN ;

LT_VBPA_MIN = SELECT MANDT, VBELN, min(POSNR) as POSNR

FROM "SLT_PRD"."VBPA"

GROUP BY MANDT, VBELN;  

LT_VBPA_AG = SELECT A.MANDT, A.VBELN, A.POSNR, B.KUNNR

FROM :LT_VBPA_MIN AS A

INNER JOIN "SLT_PRD"."VBPA" AS B

ON A.MANDT = B.MANDT

AND A.VBELN = B.VBELN

AND A.POSNR = B.POSNR

WHERE B.PARVW = 'AG';

      

     LT_VBPA_WE = SELECT A.MANDT, A.VBELN, A.POSNR, B.KUNNR

FROM :LT_VBPA_MIN AS A

INNER JOIN "SLT_PRD"."VBPA" AS B

ON A.MANDT = B.MANDT

AND A.VBELN = B.VBELN

AND A.POSNR = B.POSNR

WHERE B.PARVW = 'WE';

     LT_VBPA_RE = SELECT A.MANDT, A.VBELN, A.POSNR, B.KUNNR

FROM :LT_VBPA_MIN AS A

INNER JOIN "SLT_PRD"."VBPA" AS B

ON A.MANDT = B.MANDT

AND A.VBELN = B.VBELN

AND A.POSNR = B.POSNR

WHERE B.PARVW = 'RE';

       

var_out = SELECT A.MANDT as MANDT, A.VBELN as VBELN, B.KUNNR as KUNAG, C.KUNNR as KUNWE,

D.KUNNR as KUNRE, E.KUNNR as KUNZ3,

1 AS COUNTER

FROM :LT_VBRK as A

LEFT JOIN :LT_VBPA_AG as B

ON A.MANDT = B.MANDT

AND  A.VBELN = B.VBELN      

LEFT JOIN :LT_VBPA_WE as C

ON A.MANDT = C.MANDT

AND A.VBELN = C.VBELN

LEFT JOIN :LT_VBPA_RE as D

ON A.MANDT = D.MANDT

AND A.VBELN = D.VBELN;

However, performance gets really poor (I even get memory dumps) when joining this view in other calculation views (left join), probably because the full result set of the sql script calculation view is processed before performing the join in a second calculation view.

Any suggestions on whether this approach is correct, or how this can be optimized?

Thanks!

Tom

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

Unfortunately I don't have a test system with data in VBRK and VBPA, so I can only look at your code...

Why do you first go and find all the smallest POSNR for any given (MANDT, VBLEN) only to join it later in order to filter out non-matching entries based on PARVW?

Something like this


LT_VBPA_AG = SELECT A.MANDT, A.VBELN, A.POSNR, B.KUNNR

FROM  "SLT_PRD"."VBPA" AS B

WHERE

   (B.MANDT, B.VBELN, B.POSNR)

             IN   (SELECT MANDT, VBELN, min(POSNR) as POSNR

                   FROM "SLT_PRD"."VBPA"

                   WHERE PARVW = 'AG'

                   GROUP BY MANDT, VBELN)

AND B.PARVW = 'AG'

might yield smaller intermediate result sets.

- Lars

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Tom,

Instead of performing query on same table VBPA again and again, can you try something like below...



LT_VBRK = SELECT MANDT, VBELN

FROM "SLT_PRD"."VBRK"

WHERE VBELN = :P_VBELN ;

LT_VBPA = SELECT MANDT,VBELN,POSNR,KUNNR

FROM "SLT_PRD"."VBPA"

WHERE PARVW IN (''AG','WE','RE'');

LT_VBPA_MIN = SELECT MANDT, VBELN, min(POSNR) as POSNR

FROM :LT_VBPA

GROUP BY MANDT, VBELN;

LT_VBPA_AG = SELECT A.MANDT, A.VBELN, A.POSNR, B.KUNNR

FROM :LT_VBPA_MIN AS A

INNER JOIN LT_VBPA AS B

ON A.MANDT = B.MANDT

AND A.VBELN = B.VBELN

AND A.POSNR = B.POSNR

WHERE B.PARVW = 'AG';

LT_VBPA_WE = SELECT A.MANDT, A.VBELN, A.POSNR, B.KUNNR

FROM :LT_VBPA_MIN AS A

INNER JOIN :LT_VBPA AS B

ON A.MANDT = B.MANDT

AND A.VBELN = B.VBELN

AND A.POSNR = B.POSNR

WHERE B.PARVW = 'WE';

LT_VBPA_RE = SELECT A.MANDT, A.VBELN, A.POSNR, B.KUNNR

FROM :LT_VBPA_MIN AS A

INNER JOIN :LT_VBPA AS B

ON A.MANDT = B.MANDT

AND A.VBELN = B.VBELN

AND A.POSNR = B.POSNR

WHERE B.PARVW = 'RE';

   

var_out = SELECT A.MANDT as MANDT, A.VBELN as VBELN, B.KUNNR as KUNAG, C.KUNNR as KUNWE,

D.KUNNR as KUNRE, E.KUNNR as KUNZ3,

1 AS COUNTER

FROM :LT_VBRK as A

LEFT JOIN :LT_VBPA_AG as B

ON A.MANDT = B.MANDT

AND  A.VBELN = B.VBELN  

LEFT JOIN :LT_VBPA_WE as C

ON A.MANDT = C.MANDT

AND A.VBELN = C.VBELN

LEFT JOIN :LT_VBPA_RE as D

ON A.MANDT = D.MANDT

AND A.VBELN = D.VBELN;

-Siva