Put two queries into one query - Query - SQL - SAP B1

Question

I have a very simple Inventory in Warehouse query, and now I need to do a sum in the IsCommitted column with another query that I have called "Set Demand".

Like this = Sum( [IsCommited] + "Set Demand Query qty")

Warehouse query

SELECT T0.[ItemCode] AS 'Item No.',
T0.[WhsCode] AS 'Warehouse Code',
T0.[OnHand] AS 'In Stock',
T0.[IsCommited] AS 'Committed',
T0.[MinStock] AS 'Minimum Inventory',
T0.[MaxStock] AS 'Maximum Inventory',
T1.[ItmsGrpCod] AS 'Itemcode',
T2.Price AS 'StandardCost'
FROM [OITW] T0 INNER JOIN [OITM] T1 ON T0.ItemCode = T1.ItemCode LEFT JOIN [ITM1] T2 ON T1.ItemCode = T2.ItemCode and T2.PriceList = 26
WHERE (T0.[WhsCode] = (N'9500' )) AND (T1.[ItmsGrpCod] = (N'100' )) AND T0.[OnHand] > 0

Set Demand query.

SELECT T3.[ItemCode] ,
(-T3.[OnHand] + T1.[Quantity]) as 'Set Demand'
FROM [OITT] T0 WITH (NOLOCK) INNER JOIN [ITT1] T1 WITH (NOLOCK) ON T0.[Code] = T1.[Father], [OSRI] T2 WITH (NOLOCK), [OITW] T3 WITH (NOLOCK) INNER JOIN [OITM] T4 WITH (NOLOCK) ON T3.[ITEMCode] = T4.[ItemCode]
WHERE T0.[Code] = T2.[ItemCode] AND T1.[Code] = T3.[ItemCode] AND T2.[IntrSerial] = T3.[WhsCode] AND T2.[Status] <> 1 and T2.[U_IsCon] <> 'YES' and T3.[OnHand] - T1.[Quantity] < 0 and substring (T2.[WhsCode],8,1)<>'C' AND T2.[WhsCode] = '9000' AND t4.[ItmsGrpCod] = 100

What Im trying to do = ( [IsCommited] + "Set Demand Query qty")

SELECT T0.[ItemCode] AS 'Item No.',
T0.[WhsCode] AS 'Warehouse Code',
T0.[OnHand] AS 'In Stock',

(T0.[IsCommited] + (SELECT (-T3.[OnHand] + T1.[Quantity])

FROM [OITT] T0 WITH (NOLOCK) INNER JOIN [ITT1] T1 WITH (NOLOCK) ON T0.[Code] = T1.[Father], [OSRI] T2 WITH (NOLOCK), [OITW] T3 WITH (NOLOCK) INNER JOIN [OITM] T4 WITH (NOLOCK) ON T3.[ITEMCode] = T4.[ItemCode]
WHERE T0.[Code] = T2.[ItemCode] AND T1.[Code] = T3.[ItemCode] AND T2.[IntrSerial] = T3.[WhsCode] AND T2.[Status] <> 1 and T2.[U_IsCon] <> 'YES' and T3.[OnHand] - T1.[Quantity] < 0 and substring (T2.[WhsCode],8,1)<>'C' AND T2.[WhsCode] = '9000' AND t4.[ItmsGrpCod] = 100) as '**Commited&SetDemand**',

T0.[MinStock] AS 'Minimum Inventory',
T0.[MaxStock] AS 'Maximum Inventory',
T1.[ItmsGrpCod] AS 'Itemcode',
T2.Price AS 'StandardCost'
FROM [OITW] T0 INNER JOIN [OITM] T1 ON T0.ItemCode = T1.ItemCode LEFT JOIN [ITM1] T2 ON T1.ItemCode = T2.ItemCode and T2.PriceList = 26
WHERE (T0.[WhsCode] = (N'9500' )) AND (T1.[ItmsGrpCod] = (N'100' )) AND T0.[OnHand] > 0

Your help will be greatly appreciated. Thank you very much!!!!! :)

I would strongly recommend not to use mixed JOIN syntax (SQL-92 and SQL-89). It makes the query confusing and difficult to debug. — The Impaler, Aug 14 '21 at 15:31

score 0 · Answer 1 · answered Aug 16 '21 at 07:40

Try Something like the Below, I have added the second query with a join on the Item Code.

SELECT T0.[ItemCode] AS 'Item No.',
T0.[WhsCode] AS 'Warehouse Code',
T0.[OnHand] AS 'In Stock',
T0.[IsCommited] AS 'Committed',
T0.[MinStock] AS 'Minimum Inventory',
T0.[MaxStock] AS 'Maximum Inventory',
T1.[ItmsGrpCod] AS 'Itemcode',
T2.Price AS 'StandardCost'

, t0.[IsCommited] + demand.[Set Demand] 

FROM [OITW] T0 INNER JOIN [OITM] T1 ON T0.ItemCode = T1.ItemCode LEFT JOIN             
 [ITM1] T2 ON T1.ItemCode = T2.ItemCode and T2.PriceList = 26

  Inner Join 
(

SELECT T3.[ItemCode] ,
(-T3.[OnHand] + T1.[Quantity]) as 'Set Demand'
FROM [OITT] T0 WITH (NOLOCK) INNER JOIN [ITT1] T1 WITH (NOLOCK) ON T0.[Code] =     
T1.[Father], [OSRI] T2 WITH (NOLOCK), [OITW] T3 WITH (NOLOCK) INNER JOIN [OITM] 
T4 WITH (NOLOCK) ON T3.[ITEMCode] = T4.[ItemCode]
WHERE T0.[Code] = T2.[ItemCode] AND T1.[Code] = T3.[ItemCode] AND T2. 
[IntrSerial] = T3.[WhsCode] AND T2.[Status] <> 1 and T2.[U_IsCon] <> 'YES' and T3.[OnHand] - T1. 
[Quantity] < 0 and substring (T2.[WhsCode],8,1)<>'C' AND T2.[WhsCode] = '9000' 
AND t4.[ItmsGrpCod] = 100
) as demand on demand.ItemCode = t0.ItemCode


WHERE (T0.[WhsCode] = (N'9500' )) AND (T1.[ItmsGrpCod] = (N'100' )) AND T0. 
[OnHand] > 0

score 0 · Answer 2 · answered Aug 19 '21 at 12:18

Thank you all for your answers, much appreciated. And also sorry for my delay in answering. I was able to solve my problem through this code. Please look below. What sawsine said, was exactly that, the relationship between items was missing.

SELECT 
OITW.ItemCode,
SUM(CONVERT(INT, (ITT1.quantity - OITW.OnHand))) as 'Set Demand'

FROM OSRI
inner join OSRN on OSRN.ItemCode = OSRI.ItemCode AND OSRN.SysNumber = OSRI.SysSerial
INNER JOIN OITT ON OSRI.ItemCode = OITT.Code
INNER JOIN ITT1 ON ITT1.Father = OITT.Code
INNER JOIN OITW ON ITT1.Code = OITW.ItemCode AND OSRI.IntrSerial = OITW.WhsCode
inner join OWHS on OSRI.WhsCode = OWHS.WhsCode 

inner join OITM on ITT1.Code = OITM.ItemCode

WHERE 1=1 
AND OSRI.WhsCode = N'9000' 
and OSRI.[Status] = 0 and
(OSRN.U_Condition <> N'2'  or OSRN.U_Condition is Null) and 
OITM.ItmsGrpCod IN (100, 102, 104) and
(LEN(ISNULL(OITM.CardCode,'''')) = 0 or ISNULL(OITM.CardCode,'''') = 'NL20')
AND (ITT1.quantity - OITW.OnHand) > 0 

Group by OITW.ItemCode

Put two queries into one query - Query - SQL - SAP B1

2 Answers2