10-16-2019 11:23 AM
hi expert,
i need to get output of (plant, plant name, material, sku, description, descriptiob, loc, stock qty, avg cost, last recv.
please correct my code, because i can't get all the column with this code:
SELECT A~WERKS
C~NAME1
A~MATNR
E~BISMT
B~MAKTX
A~LGORT
SUM( CLABS ) AS SUM_CLABS
INTO TABLE T_COMB
(SELECT CASE WHEN LBKUM = 0 THEN 0 ELSE ROUND(((SALK3 * 100) / LBKUM),0)
FROM MBEW
WHERE BWKEY = A~WERKS
AND MATNR = A~MATNR) AS AVGCOST
(SELECT MAX( BUDAT_MKPF ) AS BUDAT_MKPF
FROM MSEG AS D
WHERE D~WERKS = A~WERKS
AND D~MATNR = A~MATNR
AND BWART = '101') LAST_RECV
FROM MCHB AS A
INNER JOIN MAKT AS B
ON A~MATNR = B~MATNR
INNER JOIN T001W AS C
ON A~WERKS = C~WERKS
INNER JOIN MARA AS E
ON A~MATNR = E~MATNR
WHERE A~WERKS IN ('2297','21B7')
AND LGORT = 'WR02'
AND MTART NOT IN ('ZNS','ZNV','ZSR')
GROUP BY A~WERKS C~NAME1 A~MATNR E~BISMT B~MAKTX A~LGORT
ORDER BY A~WERKS A~MATNR
10-16-2019 12:37 PM
I hope that the client requirement does not contain only "plant, plant name, material, sku, description, descriptiob, loc, stock qty, avg cost, last recv." to specify what is to be output !!!
10-17-2019 4:21 AM
sorry i forget.
i need to show column plant, plant name, material, sku, description, sloc, stock qty, avg cost, stock value, last recv, aging, batch, expired date.
please correct my coding.
10-17-2019 6:26 AM
You should start to rewrite clearly your code, using the button "Code" in the forum-editor
And you should start to reorganize the SELECT statement more clearly :
SELECT <fields to be selected>
INTO <Variable(s)>
FROM <table> // Inner join ...
WHERE <conditions> // SELECT injection
because for my point of view, your code is a big melting of everything