I am failing to write ABAP logic in SQL Using AMDP class. Please Help me to resolve this issue ,,

[rakesh_94]

Hi i am trying to calculate total Qty in MSEG table based on indicator and i wrote ALV report in ABAP its working fine , but my requirement is to write it in AMDP and consume those result in CDS view. please help me how to write below ABAP code in SQL query .

LOOP AT it_mseg INTO wa_mseg WHERE matnr = wa_mslb-matnr

AND werks = wa_mslb-erks AND lifnr = wa_mslb-lifnr.
IF sy-subrc EQ 0.
CASE wa_mseg-shkzg.
WHEN 'S'. qas = qas + wa_mseg-menge.
WHEN 'H'. qah = qah + wa_mseg-menge.
ENDCASE.
ENDIF.
CLEAR: wa_mseg.
ENDLOOP.

Thank you.

[Sandra_Rossi]

I don't see any SELECT logic in the code you have posted...

[rakesh_94]

its an ABAP code

SELECT mblnr
mjahr
zeile
werks
matnr
lifnr
sobkz
erfmg
shkzg
menge
budat_mkpf
ebeln
waers
FROM mseg
INTO CORRESPONDING FIELDS OF TABLE it_mseg
FOR ALL ENTRIES IN it_mslb
WHERE werks = it_mslb-werks AND
matnr = it_mslb-matnr AND
sobkz = it_mslb-sobkz AND
lifnr = it_mslb-lifnr AND

budat_mkpf BETWEEN asondate AND sy-datum.

[rakesh_94]

once the data get loaded into it_mseg table ,I was trying to do the posted logic and same thing I want to achieve in UISNG AMDP Class with CDS Table Function.

[ThorstenHoefer · Accepted solution]

Hi,

please try something like this:

select
   sum( case shkzg when 'S' then menge else 0 end ) as QAS
 , sum( case shkzg when 'H' then menge else 0 end ) as QAH

[rakesh_94 · Accepted solution]

Hi Thank you for the Answer but it did not gave expected result instead i wrote this query and got my answer and thank you for your input.

itab1 = select mandt ,werks, matnr, lifnr, shkzg, sum (menge) as totalqty

from nsdm_v_mseg

WHERE sobkz = 'O' AND matnr = p_matnr AND lifnr = p_lifnr

GROUP BY mandt, werks, matnr, lifnr, shkzg;

itab2 = SELECT mandt, werks, matnr, lifnr, totalqty FROM :itab1 WHERE matnr = p_matnr AND lifnr = p_lifnr AND shkzg = 'S';

itab3 = SELECT mandt, werks, matnr, lifnr, totalqty FROM :itab1 WHERE matnr = p_matnr AND lifnr = p_lifnr AND shkzg = 'H';

itab4 = SELECT a.mandt, a.werks, a.matnr, a.lifnr, ( b.totalqty - a.totalqty) as totalqty FROM :itab2 as a

INNER JOIN :itab3 as b ON a.matnr = b.matnr;

return select * from :itab4;

[rakesh_94 · Accepted solution]

Thank you sir , Its working now