topic Re: SELECT IN SELECT CODE WITH INNER JOIN in Application Development and Automation Discussions

SELECT IN SELECT CODE WITH INNER JOIN

Former Member — Wed, 16 Oct 2019 10:23:45 GMT

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

Re: SELECT IN SELECT CODE WITH INNER JOIN

Sandra_Rossi — Wed, 16 Oct 2019 11:37:47 GMT

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 !!!

Re: SELECT IN SELECT CODE WITH INNER JOIN

Former Member — Thu, 17 Oct 2019 03:21:27 GMT

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.

Re: SELECT IN SELECT CODE WITH INNER JOIN

FredericGirod — Thu, 17 Oct 2019 05:26:57 GMT

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