Skip to Content
0

Sub Query Required

Dec 09, 2016 at 10:07 AM

49

avatar image

Hi Sap Experts

I need the below query to sum and group by itemcode only. Currently I need to add T0.[U_Warranty] to the group by clause for the query to run but it then does not group and sum solely by item code which is what I require. I have looked online a from what I can see I need a sub query. Please advise if this is possible?

SELECT T0.[ItemCode], T1.[ItemName], SUM(T0.[U_Quantity]) AS 'Quantity',

Case
when T0.[U_Warranty] = 'YES'
THEN SUM(T0.[U_Quantity])
END AS 'In Warranty',

Case
when T0.[U_Warranty] = 'NO'
THEN SUM(T0.[U_Quantity])
END AS 'Out Of Warranty',

sum(T0.[U_Qty_Scrapped]) as 'Qty Scrapped', sum(T0.[U_Qty_Replaced]) as 'Qty Replaced', sum(T0.[U_Qty_Returned]) as 'Qty Returned OK'

FROM OSLT T0 INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]

GROUP BY T0.[ItemCode], T1.[ItemName]

ORDER BY T0.[ItemCode], T1.[ItemName]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Johan Hakkesteegt Dec 09, 2016 at 12:35 PM
0

Hi Chris,

Yep, that's right. Take a look at (and/or try) this query:

SELECT T0.[ItemCode]
     , T0.[ItemName]
     , (select SUM([U_Quantity])
        from OSLT
        where [ItemCode] = T0.ItemCode) AS 'Quantity'
     , (select SUM([U_Quantity])
        from OSLT
        where [U_Warranty] = 'YES'
          and [ItemCode] = T0.ItemCode) AS 'In Warranty'
     , (select SUM([U_Quantity])
        from OSLT
        where [U_Warranty] = 'NO'
          and [ItemCode] = T0.ItemCode) AS 'Out Of Warranty'
     , (select SUM([U_Qty_Scrapped])
        from OSLT
        where [ItemCode] = T0.ItemCode) as 'Qty Scrapped'
     , (select SUM([U_Qty_Replaced])
        from OSLT
        where [ItemCode] = T0.ItemCode) as 'Qty Replaced'
     , (select SUM([U_Qty_Returned])
        from OSLT
        where [ItemCode] = T0.ItemCode) as 'Qty Returned OK'
FROM OITM T0 
WHERE T0.ItemCode IN (select distinct ItemCode from OSLT)
ORDER BY T0.[ItemCode], T0.[ItemName]

Regards,

Johan

Share
10 |10000 characters needed characters left characters exceeded
Chris Fawcett Dec 09, 2016 at 02:37 PM
0

Hi Johan

Thanks you have been a great help and again your query work fine

Is there a way I can put a = [%0] clause into the above. I can filter as text such as = 'reference no' and the query works but as soon as I add the filter [%0] the query shows an error. The field I wish to filter by is not listed in the query above and is part of the OSLT table.

Thanks.

Chris

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Dec 12, 2016 at 09:59 AM
0

Hi Chris,

As soon as you start using sub queries (or link to a table outside the company database, by the way), B1 query parameters (i.e. [%0]) are a little trickier to get to work, but don't worry, it can be done. Please note however, that you may loose drill down arrows from the result.

Here is the basic idea:

/* select * from OSLT x */
DECLARE @YOURPARAMETER AS NVARCHAR(100)
SET @YOURPARAMETER = /* x.RefNumOrWhatWasTheFieldCalledAgain */ '[%0]'

-- Next your query with subqueries
-- Build you subqueries along the following lines:
(select SUM([U_Quantity])
 from OSLT
 where [ItemCode] = T0.ItemCode
  AND [RefNumOrWhatWasTheFieldCalledAgain] LIKE '%' + @YOURPARAMETER + '%') AS 'Quantity'

Please also note that any other parameters you will have to make according to the same principle.

Regards,

Johan

Share
10 |10000 characters needed characters left characters exceeded