on 03-19-2015 2:14 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.