asked on

distinct records in UNION all

Hi experts
i have a very long sql statetement with UNION ALL command, the problem that i am getting duplicate records, how can i retrieve only unique records. any suggestion

SELECT			TASK.ID as CurrentTaskId,TRK.Details,trk.ReferenceNo, 
							SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI, 
							trk.InitiatorName AS InitiatorUID, PER.DirCode,
							PER.SecCode, PER.EngSec, dbo.CurrentUserName(trk.InitiatorName) AS InitiatorName, 
							trk.RequestedDate AS RequestedDateString, 
							CAST(TRK.RequestedDate AS DATETIME) AS RequestedDate, trk.Status, 
							trk.WorkflowPID, trk.CurrentActorUID, dbo.CurrentUserName(trk.CurrentActorUID) AS CurrentActorName, 
							trk.ApprovedDate, trk.DeliveredDate, 'Out' AS InOut, PER.EngDir
			FROM			PR1.dbo.GetUserDetailsE AS PER 
			INNER JOIN		PR1.dbo.GetUserDetailsE AS PER2 ON PER.DirCode = PER2.DirCode 
			INNER JOIN		dbo.tbl_ServiceTracking AS TRK ON PER2.PERUserName = TRK.InitiatorName 
			INNER JOIN		dbo.tblService AS SER ON TRK.ServiceNo = SER.id   
			LEFT OUTER JOIN AdobeLCES2.dbo.tb_task AS TASK 
			ON				TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
			WHERE			(PER.PERUserName = 'MFFIMRF') 
			AND				CAST(TRK.RequestedDate AS DATETIME)>='9/1/2013' 
			AND				CAST(TRK.RequestedDate AS DATETIME)<='9/30/2013'
			


UNION ALL
			SELECT			TASK.ID as CurrentTaskId,TRK.Details,trk.ReferenceNo, 
							SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI, 
							trk.InitiatorName AS InitiatorUID, PER.DirCode,
							PER.SecCode, PER.EngSec, dbo.CurrentUserName(trk.InitiatorName) AS InitiatorName, 
							trk.RequestedDate AS RequestedDateString, 
							CAST(TRK.RequestedDate AS DATETIME) AS RequestedDate, trk.Status, 
							trk.WorkflowPID, trk.CurrentActorUID, dbo.CurrentUserName(trk.CurrentActorUID) AS CurrentActorName, 
							trk.ApprovedDate, trk.DeliveredDate, 'Out' AS InOut,
							PER.EngDir
			FROM			PR1.dbo.GetUserDetailsE AS PER 
			INNER JOIN		WorkFlow.dbo.tblService AS SER ON SER.DirCode = PER.DirCode 
			INNER JOIN		WorkFlow.dbo.tbl_ServiceTracking AS trk ON trk.ServiceNo = SER.id   
			LEFT OUTER JOIN AdobeLCES2.dbo.tb_task AS TASK 
			ON				TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
			WHERE           (PER.PERUserName = 'MFFIMRF') 
			AND				CAST(TRK.RequestedDate AS DATETIME)>='9/1/2013' 
			AND				CAST(TRK.RequestedDate AS DATETIME)<='9/30/2013'
			ORDER BY		RequestedDate  DESC

Open in new window

ASKER CERTIFIED SOLUTION

ButlerTechnology

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AZZA-KHAMEES

ASKER

thank you for the reply, its working now with union, but i faced another problem with ntext field i am getting this error

The ntext data type cannot be selected as DISTINCT because it is not comparable.

is there any solution instead of changing field type?

AZZA-KHAMEES

ASKER

ohh i solved it, i convert the field to nvarchar using

cast(TRK.Details as nvarchar(MAX)) as Details

Open in new window

thank you