Put two queries into one Query - SQL - SAP B1

[former_member1222750]

Hi there.

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

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

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

[azizelmir]

Hi Philipe,

Please ignore the below messages from me and try this one:

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

W0.[IsCommited] 
 +
isnull((Select Sum(T1.[Set Demand])
From
	(
	SELECT T3.[ItemCode] ,
	(-T3.[OnHand] + T1.[Quantity]) as 'Set Demand'
	FROM [OITT] W0 WITH (NOLOCK) INNER JOIN [ITT1] T1 WITH (NOLOCK) ON W0.[Code] = T1.[Father], [OSRI] T2 WITH (NOLOCK), [OITW] T3 WITH (NOLOCK) INNER JOIN [OITM] T4 WITH (NOLOCK) ON T3.[ITEMCode] = T4.[ItemCode]
	WHERE W0.[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)  T1 where T1.ItemCode=W0.ItemCode
	),0) AS 'Committed',

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

Thank you,

Aziz

[former_member1222750]

Hi Aziz,
Thank you very much.
Your last query worked perfectly.
That's exactly what it needed...

[LoHa]

Hi Phillipe,

quick and dirty:

with Demand as
(
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 T3.[OnHand] - T1.[Quantity] < 0 and substring (T2.[WhsCode],8,1)<>'C' AND T2.[WhsCode] = '9000' AND t4.[ItmsGrpCod] = 100
)
--SELECT * FROM Demand


SELECT T0.[ItemCode] AS 'Item No.',
T0.[WhsCode] AS 'Warehouse Code',
T0.[OnHand] AS 'In Stock',
T0.[IsCommited] AS 'Committed',
T0.[IsCommited] + (SELECT ISNULL(Demand.Set_Demand,0) FROM Demand WHERE Demand.ItemCode = T0.ItemCode) AS 'added',
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] = ('9500' )) AND (T1.[ItmsGrpCod] = ('100' )) AND T0.[OnHand] > 0

It would work with a subselect as you mentioned as well, but you did not conected it with the itemcode. You also used the same table-alias, so you can't use the t0.itemcode from your warehouse query.

regards Lothar

[azizelmir]

Hi Philipe,

Please try this code:

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

T0.[IsCommited] 
 +
isnull((Select Sum(T1.[Set Demand])
From
	(
	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) T1
	),0) 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'01' )) AND (T1.[ItmsGrpCod] = (N'120' )) AND T0.[OnHand] > 0

Thank you,

Aziz

[azizelmir]

Hi Philipe,

Please try this code:

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

T0.[IsCommited] 
 +
isnull((Select Sum(T1.[Set Demand])
From
	(
	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) T1
	),0) 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'01' )) AND (T1.[ItmsGrpCod] = (N'120' )) AND T0.[OnHand] > 0

Thank you,

Aziz

[azizelmir]

Hi Philipe,

Please try this code:

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

T0.[IsCommited] 
 +
isnull((Select Sum(T1.[Set Demand])
From
	(
	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) T1
	),0) 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'01' )) AND (T1.[ItmsGrpCod] = (N'120' )) AND T0.[OnHand] > 0

Thank you,

Aziz

[azizelmir]

Hi Philip,

Please try this code:

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

T0.[IsCommited] 
 +
isnull((Select Sum(T1.[Set Demand])
From
	(
	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) T1
	),0) 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'01' )) AND (T1.[ItmsGrpCod] = (N'120' )) AND T0.[OnHand] > 0

Thank you,

Aziz