Shahrukh04
Member
I have this SQL query and I want to see Total_Manufacturing_Quantity and Average_Cost from my sub query into my main select statement. I don't have any clue further then this query please advice and help me.
SELECT A.SDLITM ITEM_NUMBER,
A.SDDSC1 ITEM_DESCRIPTION,
A.SDMCU BUSINESS_UNIT,
A.SDUNCS UNIT_COST,
A.SDUPRC UNIT_PRICE,
A.SDIVD INVOICE_DATE,
B.LIPQOH QUANTITY_ON_HAND
from F4211 A, F41021 B
Where A.SDDOCO = 1715
AND A.SDITM = B.LIITM
AND A.SDMCU = B.LIMCU
AND A.SDLITM = (SELECT
SUM(C.ILTRQT) TOTAL_MANUFACTURED_QUANTITY
FROM F4111 C
WHERE C.ILLITM = A.SDLITM
AND C.ILDCT = 'IC' ) TOTAL_MANUFACTURE
AND A.SDLITM = (SELECT
SUM(D.SDECST)/SUM(D.SDSOQS) AVERAGE_COST
FROM F4211 D
WHERE D.SDNXTR = '999'
AND D.SDLTTR != '980'
AND D.SDDOC > '0'
AND D.SDLITM = A.SDLITM
AND D.SDMCU = A.SDLITM) AVERAGE_COST
GROUP BY A.SDLITM,A.SDDSC1, A.SDMCU, A.SDUNCS,A.SDUPRC, A.SDIVD, B.LIPQOH
Kind Regards,
Shahrukh
SELECT A.SDLITM ITEM_NUMBER,
A.SDDSC1 ITEM_DESCRIPTION,
A.SDMCU BUSINESS_UNIT,
A.SDUNCS UNIT_COST,
A.SDUPRC UNIT_PRICE,
A.SDIVD INVOICE_DATE,
B.LIPQOH QUANTITY_ON_HAND
from F4211 A, F41021 B
Where A.SDDOCO = 1715
AND A.SDITM = B.LIITM
AND A.SDMCU = B.LIMCU
AND A.SDLITM = (SELECT
SUM(C.ILTRQT) TOTAL_MANUFACTURED_QUANTITY
FROM F4111 C
WHERE C.ILLITM = A.SDLITM
AND C.ILDCT = 'IC' ) TOTAL_MANUFACTURE
AND A.SDLITM = (SELECT
SUM(D.SDECST)/SUM(D.SDSOQS) AVERAGE_COST
FROM F4211 D
WHERE D.SDNXTR = '999'
AND D.SDLTTR != '980'
AND D.SDDOC > '0'
AND D.SDLITM = A.SDLITM
AND D.SDMCU = A.SDLITM) AVERAGE_COST
GROUP BY A.SDLITM,A.SDDSC1, A.SDMCU, A.SDUNCS,A.SDUPRC, A.SDIVD, B.LIPQOH
Kind Regards,
Shahrukh