Skip to Content
0

Query duplicating lines

Nov 24, 2016 at 02:31 PM

58

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
DIEGO LOTHER Nov 30, 2016 at 10:54 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
DIEGO LOTHER Nov 25, 2016 at 04:31 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Gonzalo Gomez Nov 28, 2016 at 08:59 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Ebie Conrad Nov 29, 2016 at 06:54 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded