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

Hi.  I'm confused about the error message I'm receiving when I try to run the attached Union query.  The problem appears to be with the two text fields on either side of the Union but I don't understand why.  I haven't used the word DISTINCT in my query (though you would think I had from reading the error message).   Can anyone help solve this?  Thanks
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE spPetServiceHistoryFull 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT     tblCustomers.fldLastNm AS [Last Name], 
			tblPets.fldPetName AS [Pet Name], 
			tblInvoices.fldDate AS Date, 
			tblVisitTypes.fldVisitType AS Service, 
			tblInvoiceDetails.fldUnits * tblInvoiceDetails.fldUnitPrice AS Price, 
			tblInvoiceDetails.fldNotes AS Comments, 
			tblEmployees.fldInitials AS Provider
	FROM        tblVisitTypes INNER JOIN
				tblEmployees RIGHT OUTER JOIN
				tblCustomers INNER JOIN
				tblInvoices INNER JOIN
				tblInvoiceDetails ON tblInvoices.fldInvID = tblInvoiceDetails.fldInvID ON tblCustomers.fldCustID = tblInvoices.fldCustID INNER JOIN
				tblPets ON tblInvoiceDetails.fldPetID = tblPets.fldPetID ON tblEmployees.fldEmplID = tblInvoiceDetails.fldEmplID ON 
				tblVisitTypes.fldVisitTypeID = tblInvoiceDetails.fldItemID
	WHERE     (tblPets.fldPetID = 3343) AND (tblInvoices.fldVoid = 0)
	UNION
	SELECT     tblCustomers_1.fldLastNm AS [Last Name], 
			tblPets_1.fldPetName AS [Pet Name], 
			tblPetVisits.fldDate AS Date, 
			tblVisitTypes_1.fldVisitType AS Service, 
			NULL AS Price, 
			tblPetVisits.fldComments AS Comments, 
			tblEmployees_1.fldInitials AS Provider
	FROM         tblEmployees AS tblEmployees_1 RIGHT OUTER JOIN
				tblVisitTypes AS tblVisitTypes_1 INNER JOIN
				tblCustomers AS tblCustomers_1 INNER JOIN
				tblPets AS tblPets_1 ON tblCustomers_1.fldCustID = tblPets_1.fldCustID INNER JOIN
				tblPetVisits ON tblPets_1.fldPetID = tblPetVisits.fldPetID ON tblVisitTypes_1.fldVisitTypeID = tblPetVisits.fldVisitTypeID ON 
				tblEmployees_1.fldEmplID = tblPetVisits.fldProvider
	WHERE     (tblPets_1.fldPetID = 3343)
	ORDER BY Date
END
GO

Open in new window

LVL 1
penlandtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Union implies distinct.

Change that to union all.

HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
penlandtAuthor Commented:
I learned something new today.  Thanks!  That fixed it.  Points to follow...
0
penlandtAuthor Commented:
Fast and accurate.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.