9 Replies Latest reply: Feb 26, 2012 10:08 PM by Pari Minhas RSS

Problem with Query when I try to do more than one group

Paul Masterson
Currently Being Moderated

Hi Sap Experts,

 

Hopefully somebody can suggest a way to make my query work a bit better. I have built a stock warehouse query which prompt for item groupcode

 

see 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 (T0.[ItmsGrpNam] >= (N'[%0]' ) ) AND  (T0.[ItmsGrpNam] <= (N'[%1]' ) ) AND  (T2.[OnHand] > (0 ) )  ORDER BY T2.[WhsCode]

 

 

At the moment this is working fine when I select to and from a group with the same ItemCode- however I now would like to to add 2/3 itemgroupcodes to the same query.

 

So at the moment if I run this - it promts for the Item Group Code - I enter the to/from itemcode - the query gives me a breakdown of the item group , warehouse location for that item and if there is stock on hand for each location

 

However I would like to be able to select 3 distinct Item groups but don't see to be able to when I adjust the query - can you add multiple parameter to add the 3 distinct itemgroup codes

 

If any one could suggest a way to alter the query to allow to choose 3/4 distinct groups either using the [%1] selections or simply built into the query that would be great

 

 

thanking you in advance for any suggestions you may have

 

Paul

  • Re: Problem with Query when I try to do more than one group
    Gordon Du
    Currently Being Moderated

    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

    • Re: Problem with Query when I try to do more than one group
      Paul Masterson
      Currently Being Moderated

      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

      • Re: Problem with Query when I try to do more than one group
        Paul Masterson
        Currently Being Moderated

        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

        • Re: Problem with Query when I try to do more than one group
          István Korös
          Currently Being Moderated

          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)

           

          • Re: Problem with Query when I try to do more than one group
            Paul Masterson
            Currently Being Moderated

            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

             

            • Re: Problem with Query when I try to do more than one group
              Gordon Du
              Currently Being Moderated

              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)

               

              • Re: Problem with Query when I try to do more than one group
                Paul Masterson
                Currently Being Moderated

                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

                • Re: Problem with Query when I try to do more than one group
                  Gordon Du
                  Currently Being Moderated

                  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.

                • Re: Problem with Query when I try to do more than one group
                  Pari Minhas
                  Currently Being Moderated

                  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

Actions