Duplicated Row entry in query

[former_member351438]

Experts,

Im making report base on Opportunity module of SAP b1. Im just trying to get the Opportunity name, Company name, Percentage and current stage. Now my problem is whenever there is new activity/entry in Stages column the sequence ID is getting duplicated. Please see the attached image and see opportunity number 9.

Thank you in advance

here is my super entry code.

SELECT T0.[OpprId], T0.[Name], T0.[CardName], T1.[IndName], CASE WHEN T0.[CloPrcnt]='5' THEN 'Negotiation' WHEN T0.[CloPrcnt]='20' THEN 'Demonstration' WHEN T0.[CloPrcnt]='40' THEN 'Quotation' WHEN T0.[CloPrcnt]='80' THEN 'Negotation' WHEN T0.[CloPrcnt]='80' THEN 'Sales Order' WHEN T0.[CloPrcnt]='90' THEN 'Purchasing' WHEN T0.[CloPrcnt]='100' THEN 'Invoice'
ELSE null END AS 'Current Stage',T3.[DocNumber],T0.[CloPrcnt], T0.[MaxSumLoc], T0.[WtSumLoc],T0.[OpenDate], T0.[PredDate],CASE WHEN T0.[OpenDate] = T0.[PredDate] THEN 'WHY STILL OPEN? PLEASE UPDATE!!' ELSE null END AS 'SYSTEM REMARKS', T2.[SlpName], CASE WHEN T0.[Status]='O' THEN 'OPEN' WHEN T0.[Status]='L' THEN 'LOSS' WHEN T0.[Status]='W' THEN 'WON' ELSE NULL END AS 'Status' FROM OOPR T0  INNER JOIN OOND T1 ON T0.[Industry] = T1.[IndCode] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] INNER JOIN OPR1 T3 ON T0.[OpprId] = T3.[OpprId]


WHERE T0.[Status] = 'O' or T0.[Status] = 'W'

[azizelmir]

Dear Dect,

Please try this modification in your query:

SELECT DISTINCT	
	
		T0.[OpprId], 
		T0.[Name], 
		T0.[CardName], 
		T1.[IndName], 
		CASE WHEN T0.[CloPrcnt]='5' THEN 'Negotiation' WHEN T0.[CloPrcnt]='20' THEN 'Demonstration' WHEN T0.[CloPrcnt]='40' THEN 'Quotation' WHEN T0.[CloPrcnt]='80' THEN 'Negotation' WHEN T0.[CloPrcnt]='80' THEN 'Sales Order' WHEN T0.[CloPrcnt]='90' THEN 'Purchasing' WHEN T0.[CloPrcnt]='100' THEN 'Invoice'
			ELSE null END AS 'Current Stage',
		T3.[DocNumber],
		T0.[CloPrcnt], 
		T0.[MaxSumLoc], 
		T0.[WtSumLoc],
		T0.[OpenDate], 
		T0.[PredDate],
		CASE WHEN T0.[OpenDate] = T0.[PredDate] THEN 'WHY STILL OPEN? PLEASE UPDATE!!' ELSE null END AS 'SYSTEM REMARKS',
		T2.[SlpName], 
		CASE WHEN T0.[Status]='O' THEN 'OPEN' WHEN T0.[Status]='L' THEN 'LOSS' WHEN T0.[Status]='W' THEN 'WON' ELSE NULL END AS 'Status' 
FROM OOPR T0  INNER JOIN OOND T1 ON T0.[Industry] = T1.[IndCode] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] INNER JOIN OPR1 T3 ON T0.[OpprId] = T3.[OpprId]

WHERE T0.[Status] = 'O' or T0.[Status] = 'W'

Thank you,

Aziz

[former_member351438]

Mr Aziz,

Thanks for the reply. But it did not work. I also use DISTINCT before.