3 weeks ago
I have created a query. But it adds the value multiple times. Kindly provide a solution.
SELECT DISTINCT T0."DocEntry", T0."DocNum", T0."ItemCode", T0."ProdName",SUM(T1."Quantity"*T1."StockPrice"),SUM(T2."Quantity"*T2."StockPrice")
FROM OWOR T0
Left JOIN IGE1 T1 ON T1."BaseEntry" =T0."DocEntry" AND T1."BaseRef" = T0."DocNum" AND T1."BaseType" =202
Left JOIN IGN1 T2 ON T2."BaseEntry" =T0."DocEntry"
WHERE T0."DocNum" = '31457'
GROUP BY T0."DocEntry",T0."DocNum",T0."ItemCode", T0."ProdName"
SAP Enterprise Support SAP Business One, version for SAP HANA SAP ERP for Business All-in-One, manufacturing edition SAP Field Service Management, connector for SAP Business One SAP Business One
Request clarification before answering.
Hi @Deepak33 ,
I don't know if this will work for you or not.
SELECT
X."BaseRef",
X."BaseEntry",
(SELECT "ItemCode" FROM OWOR WHERE OWOR."DocNum"=X."BaseRef") AS "FG_CODE",
(SELECT "ProdName" FROM OWOR WHERE OWOR."DocNum"=X."BaseRef") AS "FG_NAME",
SUM(X."FG_PRICE") AS "FG_PRICE",
SUM(X."RM_PRICE") AS "RM_PRICE"
FROM
(
SELECT T0."BaseRef", T0."BaseEntry", SUM(T0."Quantity"* T0."StockPrice") AS "FG_PRICE" ,0 AS "RM_PRICE"
FROM IGN1 T0
WHERE T0."BaseType" =202
GROUP BY T0."BaseRef", T0."BaseEntry"
UNION ALL
SELECT T0."BaseRef", T0."BaseEntry", 0 AS "FG_PRICE",SUM(T0."Quantity"* T0."StockPrice") AS "RM_PRICE"
FROM IGE1 T0
WHERE T0."BaseType" =202
GROUP BY T0."BaseRef", T0."BaseEntry"
)X
WHERE X."BaseRef"=124020350
GROUP BY X."BaseRef", X."BaseEntry"
Thanks.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Your solution is working, thanks for the reply. How do I add date selection from IGN1?
WHERE T2."DocDate" >=[%0\] AND T2."DocDate" <=[%1\]
Hi @Deepak33 ,
/* Select * From OIGN T0 Where T0."DocDate" = [%0] */
/* Select * From OWOR T0 Where T0."DocNum" = [%1] */
SELECT
X."BaseRef",
X."BaseEntry",
(SELECT "ItemCode" FROM OWOR WHERE OWOR."DocNum"=X."BaseRef") AS "FG_CODE",
(SELECT "ProdName" FROM OWOR WHERE OWOR."DocNum"=X."BaseRef") AS "FG_NAME",
SUM(X."FG_PRICE") AS "FG_PRICE",
SUM(X."RM_PRICE") AS "RM_PRICE"
FROM
(
SELECT T0."BaseRef", T0."BaseEntry", SUM(T0."Quantity"* T0."StockPrice") AS "FG_PRICE" ,0 AS "RM_PRICE"
FROM IGN1 T0
INNER JOIN OIGN T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0."BaseType" =202 AND T1."DocDate" = [%0]
GROUP BY T0."BaseRef", T0."BaseEntry"
UNION ALL
SELECT T0."BaseRef", T0."BaseEntry", 0 AS "FG_PRICE",SUM(T0."Quantity"* T0."StockPrice") AS "RM_PRICE"
FROM IGE1 T0
WHERE T0."BaseType" =202
GROUP BY T0."BaseRef", T0."BaseEntry"
)X
WHERE X."BaseRef"=[%1]
GROUP BY X."BaseRef", X."BaseEntry"
You can use like this.
Thanks.
Manish.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
120 | |
10 | |
8 | |
5 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.