Hi Paul,
Your query works with multiple item groups as long as their names are in the selected range. What is your exact need? Give an example.
Thanks,
Gordon
Hi Gordon,
You are the right - at the moment the query works within a select range to/from - but one is outside the range
However I need to adjust the query to select 3/4 itemgroupcodename that do not follow each other in the range. If I check the itemgroupcode for each one there are not all in sequence - so i have 130,131,134,154
Obviously I dont want to select anything except the specific itemgroupnames - so how should i alter the query - this query began as a selection query for itemgroupcode name.
I know need to simply compile 3/4 itemgroupname codes for a report which will be run off once a month.
The query as you can see will give me a breakdown of the item group code group and the warehouse location for each one where there is on hand stock.
It is similare to the standard stock by warehouse report in sap but the customer wanted to see specifics field on top.
One other thing to ask but I think I already know the answer from reading ur book:)
Can I subtotal the onhand stock by each item group location in the query( so there may be 15 locations per itemgroupcode). I do remember you pointing out this can be trickty within the Query Generator in SAP.
thanking in advance as ever for ur suggestions
Paul
Hi Gordan,
I have been playing with the query and the conclusion i have come to is this - this way works selecting one itemgroupcode
SELECT T1.[ItemCode] AS 'Item No.', T1.[ItemName] AS 'Item Description', T1.[BuyUnitMsr] AS 'Purchasing UoM', T1.[AvgPrice] AS 'Item Cost', T2.[OnHand] AS 'In Stock', T2.[OnHand] * T1.[AvgPrice] AS 'On-Hand Qty*Unit Price', T1.[PurFactor1] AS 'Purchasing Factor 1', T2.[OnHand] * T1.[PurFactor1] AS 'On-Hand*Purchase Factor', T2.[WhsCode] AS 'Warehouse Code', T3.[WhsName] AS 'Warehouse Name' FROM [dbo].[OITB] T0 INNER JOIN [dbo].[OITM] T1 ON T1.[ItmsGrpCod] = T0.[ItmsGrpCod] INNER JOIN [dbo].[OITW] T2 ON T2.[ItemCode] = T1.[ItemCode] INNER JOIN [dbo].[OWHS] T3 ON T3.[WhsCode] = T2.[WhsCode] WHERE (T2.[OnHand] > (0 ) ) AND (T1.[ItmsGrpCod] = (N'130' ))
This query works by it self and selects the Item Group Code that I need to see.
However I can't seem to get it to work If I try to add a further group code? see code below
SELECT T1.[ItemCode] AS 'Item No.', T1.[ItemName] AS 'Item Description', T1.[BuyUnitMsr] AS 'Purchasing UoM', T1.[AvgPrice] AS 'Item Cost', T2.[OnHand] AS 'In Stock', T2.[OnHand] * T1.[AvgPrice] AS 'On-Hand Qty*Unit Price', T1.[PurFactor1] AS 'Purchasing Factor 1', T2.[OnHand] * T1.[PurFactor1] AS 'On-Hand*Purchase Factor', T2.[WhsCode] AS 'Warehouse Code', T3.[WhsName] AS 'Warehouse Name' FROM [dbo].[OITB] T0 INNER JOIN [dbo].[OITM] T1 ON T1.[ItmsGrpCod] = T0.[ItmsGrpCod] INNER JOIN [dbo].[OITW] T2 ON T2.[ItemCode] = T1.[ItemCode] INNER JOIN [dbo].[OWHS] T3 ON T3.[WhsCode] = T2.[WhsCode] WHERE (T2.[OnHand] > (0 ) ) AND (T1.[ItmsGrpCod] = (N'130' )) AND (T1.[ItmsGrpCod] = (N'135' ))
It just seems to fail even though I know there is data for both groups - Have i missed something in the code?
I would like to be able to add 3 further group codes to the query as it will be for a report showing stock on hand once a month in the various locations
Any suggestion as to who to tweak the addtional groups
thanks
Paul
Try this code to select max 4 groups:
declare @g0 int ,@g1 int ,@g2 int ,@g3 int
/*SELECT T0.[ItmsGrpCod], T0.[ItmsGrpNam] FROM OITB T0 WHERE T0.[ItmsGrpCod] in ('[%0]','[%1]','[%2]','[%3]')*/
set @g0='[%0]'
set @g1='[%1]'
set @g2='[%2]'
set @g3='[%3]'
SELECT T1.ItemCode AS 'Item No.', T1.ItemName AS 'Item Description',
T1.BuyUnitMsr AS 'Purchasing UoM', T1.AvgPrice AS 'Item Cost',
T2.OnHand AS 'In Stock', T2.OnHand * T1.AvgPrice AS 'On-Hand Qty*Unit Price',
T1.PurFactor1 AS 'Purchasing Factor 1',
T2.OnHand * T1.PurFactor1 AS 'On-Hand*Purchase Factor',
T2.WhsCode AS 'Warehouse Code', T3.WhsName AS 'Warehouse Name'
FROM dbo.OITB T0
INNER JOIN dbo.OITM T1 ON T1.ItmsGrpCod = T0.ItmsGrpCod
INNER JOIN dbo.OITW T2 ON T2.ItemCode = T1.ItemCode
INNER JOIN dbo.OWHS T3 ON T3.WhsCode = T2.WhsCode
WHERE (T2.OnHand > (0 ) ) AND T1.ItmsGrpCod in (@g0,@g1,@g2,@g3)
Hi István,
thank you for that - That is now giving me the 4 group I want to pull out. I have one small request on the query
Based on the query - can I change the criteria so instead of ItmsGrpCod as the variable - can I use ItmsGrpNam as the option to select? Reason being it will look clearer to the user selecting the group name rather that the code. It work either way just easier for the user to understand.
I presume it doable and I assume i need to declare the variables as something other than int?
thank you from coming back with the response so quickly
Paul
Hi Paul,
Try:
declare @g0 nvarchar(20),@g1 nvarchar(20),@g2 nvarchar(20),@g3 nvarchar(20)
/*SELECT T0.[ItmsGrpNam] FROM OITB T0 WHERE T0.[ItmsGrpNam] in ('[%0]','[%1]','[%2]','[%3]')*/
set @g0='[%0]'
set @g1='[%1]'
set @g2='[%2]'
set @g3='[%3]'
SELECT T1.ItemCode AS 'Item No.', T1.ItemName AS 'Item Description',
T1.BuyUnitMsr AS 'Purchasing UoM', T1.AvgPrice AS 'Item Cost',
T2.OnHand AS 'In Stock', T2.OnHand * T1.AvgPrice AS 'On-Hand Qty*Unit Price',
T1.PurFactor1 AS 'Purchasing Factor 1',
T2.OnHand * T1.PurFactor1 AS 'On-Hand*Purchase Factor',
T2.WhsCode AS 'Warehouse Code', T3.WhsName AS 'Warehouse Name'
FROM dbo.OITB T0
INNER JOIN dbo.OITM T1 ON T1.ItmsGrpCod = T0.ItmsGrpCod
INNER JOIN dbo.OITW T2 ON T2.ItemCode = T1.ItemCode
INNER JOIN dbo.OWHS T3 ON T3.WhsCode = T2.WhsCode
WHERE (T2.OnHand > 0 ) AND T0.ItmsGrpNam in (@g0,@g1,@g2,@g3)
Hi Gordon,
This works for me now. For the moment I am going to give the user the option to select the group codes using the selection variables.
My final question with regards to this is - if the user eventually settles on the 4 groups that they want to see each month - can the query be adjusted to hardcode the values that you would normally enter by selection
So instead of choosing the %0]','[%1]','[%2]','[%3] we have 'Fans', 'Blades', 'SheetMetal', "Tubes" in our query
declare @g0 nvarchar(20),@g1 nvarchar(20),@g2 nvarchar(20),@g3 nvarchar(20)
/SELECT T0.[ItmsGrpNam] FROM OITB T0 WHERE T0.[ItmsGrpNam] in ('[%0]','[%1]','[%2]','[%3]')/
set @g0='[%0]'
set @g1='[%1]'
set @g2='[%2]'
set @g3='[%3]'
SELECT T1.ItemCode AS 'Item No.', T1.ItemName AS 'Item Description',
T1.BuyUnitMsr AS 'Purchasing UoM', T1.AvgPrice AS 'Item Cost',
T2.OnHand AS 'In Stock', T2.OnHand * T1.AvgPrice AS 'On-Hand Qty*Unit Price',
T1.PurFactor1 AS 'Purchasing Factor 1',
T2.OnHand * T1.PurFactor1 AS 'On-Hand*Purchase Factor',
T2.WhsCode AS 'Warehouse Code', T3.WhsName AS 'Warehouse Name'
FROM dbo.OITB T0
INNER JOIN dbo.OITM T1 ON T1.ItmsGrpCod = T0.ItmsGrpCod
INNER JOIN dbo.OITW T2 ON T2.ItemCode = T1.ItemCode
INNER JOIN dbo.OWHS T3 ON T3.WhsCode = T2.WhsCode
WHERE (T2.OnHand > 0 ) AND T0.ItmsGrpNam in (@g0,@g1,@g2,@g3)
As of now i am happy to leave it like that and but eventually I would like to hardcode in the groups.
again thank you both for the quick response
Paul
If you want to hard coded, it is easier.
Replace the last condition as: T0.ItmsGrpNam in ('Fans', 'Blades', 'SheetMetal', 'Tubes')
Then remove all leading variables.
However, the better option is using T0.ItmsGrpCod to hardcode this query.
Hi
Your final Query will be like this :
SELECT T1.ItemCode AS 'Item No.', T1.ItemName AS 'Item Description',
T1.BuyUnitMsr AS 'Purchasing UoM', T1.AvgPrice AS 'Item Cost',
T2.OnHand AS 'In Stock', T2.OnHand * T1.AvgPrice AS 'On-Hand Qty*Unit Price',
T1.PurFactor1 AS 'Purchasing Factor 1',
T2.OnHand * T1.PurFactor1 AS 'On-Hand*Purchase Factor',
T2.WhsCode AS 'Warehouse Code', T3.WhsName AS 'Warehouse Name'
FROM dbo.OITB T0
INNER JOIN dbo.OITM T1 ON T1.ItmsGrpCod = T0.ItmsGrpCod
INNER JOIN dbo.OITW T2 ON T2.ItemCode = T1.ItemCode
INNER JOIN dbo.OWHS T3 ON T3.WhsCode = T2.WhsCode
WHERE (T2.OnHand > 0 ) AND T0.ItmsGrpNam in ('Fans', 'Blades', 'SheetMetal', 'Tubes')
Regards