Skip to Content

Query duplicating lines

Hi

This query i got off the SCN site.

it works etc and is what i need.

Problem seems to be that line items are duplicated and based on the attached screenshot it seems to be on items where changes has been made to the description field. I would assume it is the the table that store the history? is there a way to only populate the last or latest description instead of duplicating the item? i have this same problem with other reports written and would appreciate a solution to prevent this duplication.

Regards

sap-query.png (123.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Nov 30, 2016 at 10:54 AM

    Hi Ebie,

    A new query:

    SELECT 
    	DISTINCT p.ItemCode, 
    	p.ItemName, 
    	[1] as 'Jan 16', 
    	[2] as 'Feb 16', 
    	[3] as 'Mar 16', 
    	[4] as 'Apr 16', 
    	[5] as 'May 16', 
    	[6] as 'Jun 16', 
    	[7] as 'Jul 16', 
    	[8] as 'Aug 16', 
    	[9] as 'Sep 16', 
    	[10] as 'Oct 16', 
    	[11] as 'Nov 16', 
    	[12] as 'Dec 16' 
    FROM 
    	(SELECT 
    		DISTINCT T0.ItemCode, 
    		T2.ItemName, 
    		T0.Quantity AS Qty, 
    		MONTH(T0.DocDate) AS month 
    	 FROM 
    		INV1 T0 
    		INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
    		INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode
    	WHERE 
    		YEAR(T1.DocDate) = 2016 
    		AND T0.ItemCode >= '%vat%' 
    		AND T0.ItemCode NOT LIKE 'disc%%' 
    		AND T0.ItemCode NOT LIKE 'K______%%' 
    		AND T0.ItemCode NOT LIKE 'x70____%%' 
    		AND T0.ItemCode NOT LIKE 'K_____%%' 
    		AND T0.ItemCode NOT LIKE 'A7_____%%' 
    		AND T0.ItemCode NOT LIKE 'A8_____%%' 
    		AND T0.ItemCode NOT LIKE 'A3______%%' 
    	UNION 
    	SELECT 
    		DISTINCT T0.ItemCode, 
    		T2.ItemName,
    		T0.Quantity as Qty, 
    		MONTH(T0.DocDate) as month 
    	FROM 
    		RIN1 T0 
    		INNER JOIN ORIN T1 ON T0.DocEntry = T1.DocEntry 
    		INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode
    	WHERE 
    		YEAR(T1.DocDate) = 2016 
    		AND T0.ItemCode >= '%vat%' 
    		AND T0.ItemCode NOT LIKE 'disc%%' 
    		AND T0.ItemCode NOT LIKE 'K______%%' 
    		AND T0.ItemCode NOT LIKE 'x70____%%' 
    		AND T0.ItemCode NOT LIKE 'K_____%%' 
    		AND T0.ItemCode NOT LIKE 'A7_____%%' 
    		AND T0.ItemCode NOT LIKE 'A8_____%%' 
    		AND T0.ItemCode NOT LIKE 'A3______%%' 
    	) S PIVOT (SUM(Qty) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P ORDER BY p.itemcode
    

    But I believe your query brings wrong results, because your distinct instruction in your subquery, avoid a lot of results.

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 25, 2016 at 04:31 PM

    Hi Ebie,

    Could you share your query?

    About your scenario, one solution is to join OITM table with its document line table and show the value from OITM.ItemName instead dscription.

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 28, 2016 at 08:59 AM

    add in the select max(t0.itemname) and remove it from de group by

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 29, 2016 at 06:54 AM

    here is the query

    SELECT distinct p.itemcode, p.dscription, [1] as 'Jan 16', [2] as 'Feb 16', [3] as 'Mar 16', [4] as 'Apr 16', [5] as 'May 16', [6] as 'Jun 16', [7] as 'Jul 16', [8] as 'Aug 16', [9] as 'Sep 16', [10] as 'Oct 16', [11] as 'Nov 16', [12] as 'Dec 16' FROM (SELECT distinct T0.ItemCode, T0.Dscription, T0.Quantity as Qty, MONTH(T0.DocDate) as month FROM inv1 T0 Inner Join OINV T1 ON T0.DocEntry = T1.DocEntry WHERE Year(T1.DocDate) = 2016 and T0.ItemCode >= '%vat%' and T0.ItemCode not like 'disc%%' and T0.ItemCode not like 'K______%%' and T0.ItemCode not like 'x70____%%' and T0.ItemCode not like 'K_____%%' and T0.ItemCode not like 'A7_____%%' and T0.ItemCode not like 'A8_____%%' and T0.ItemCode not like 'A3______%%' UNION SELECT distinct T0.ItemCode, T0.Dscription, T0.Quantity as Qty, MONTH(T0.DocDate) as month FROM RIN1 T0 Inner Join ORIN T1 ON T0.DocEntry = T1.DocEntry WHERE Year(T1.DocDate) = 2016 and T0.ItemCode >= '%vat%' and T0.ItemCode not like 'disc%%' and T0.ItemCode not like 'K______%%' and T0.ItemCode not like 'x70____%%' and T0.ItemCode not like 'K_____%%' and T0.ItemCode not like 'A7_____%%' and T0.ItemCode not like 'A8_____%%' and T0.ItemCode not like 'A3______%%' ) S PIVOT (SUM(Qty) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P order by p.itemcode

    Add comment
    10|10000 characters needed characters exceeded