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?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Union implies distinct.

Change that to union all.

HTH
  David
0
 
penlandtAuthor Commented:
I learned something new today.  Thanks!  That fixed it.  Points to follow...
0
 
penlandtAuthor Commented:
Fast and accurate.  Thanks!
0
All Courses

From novice to tech pro — start learning today.