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

Question

I have a query which is joined by multiple queries and multiple tables if i run this query I am getting an error like this :

1. The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
2. The ntext data type cannot be selected as DISTINCT because it is not comparable.

The table structure is:

and the query is this :

SELECT p.Proj_uid, p.proj_name,p.Agency,p.District,p.Division,p.ProjStatus,Civilbill80.billcount as Civilbill80, 
Civilbill20.billcount as Civilbill20 ,Civilbillpay.billcount as FinalCivilBill,CivilWorkslip.billcount as CivilWorkslip,
 Electribill80.billcount as Electricbill80, Electribill20.billcount as Electricbill20, Electribillpay.billcount as FinalElectriBill,
 ElectriWorkslip.billcount as ElectriWorkslip  FROM tempproj p
  LEFT JOIN (
             SELECT distinct(Proj_name),BillType,COUNT(1) as billcount  FROM payment_80   where BillType='CIVIL'
              GROUP BY Proj_name, BillType ) Civilbill80 ON Civilbill80.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),billtype,COUNT(1) as billcount FROM Payment_20    where billtype='CIVIL'
              GROUP BY Proj_name, billtype ) Civilbill20 ON Civilbill20.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),BillType, COUNT(1) as billcount  FROM payment_80  where BillType='Electric'
              GROUP BY Proj_name, BillType  ) Electribill80 ON Electribill80.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),billtype, COUNT(1) as billcount  FROM Payment_20  where billtype='Electric'
              GROUP BY Proj_name, billtype ) Electribill20 ON Electribill20.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),billtype, COUNT(1) as billcount   FROM Payment  where billtype='CIVIL'
              GROUP BY Proj_name, billtype ) Civilbillpay ON Civilbillpay.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),billtype, COUNT(1) as billcount  FROM Payment  where billtype='CIVIL'
              GROUP BY Proj_name, billtype  ) Electribillpay ON Electribillpay.Proj_name = p.proj_name
LEFT JOIN (
             SELECT distinct(proj_uid),item_type, COUNT(1) as billcount  FROM WorkSlipAmounts where item_type='WorkSlip'
              GROUP BY proj_uid, item_type ) CivilWorkslip ON CivilWorkslip.proj_uid = p.proj_uid
 LEFT JOIN (
             SELECT distinct(proj_uid),item_type, COUNT(1) as billcount  FROM WorkSlipAmounts  where item_type='ElecWorkSlip'
              GROUP BY proj_uid, item_type) ElectriWorkslip ON ElectriWorkslip.proj_uid = p.proj_uid

Please help me out . Thank you so much

Answer 1

SQL Server's ntext, text, and image datatypes are obsolete:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Possible solution:

1. Identify columns that use these datatypes

2a. (Correct way) Alter table and change datatypes to NVARCHAR/VARBINARY

2b. (Workaround) Or in SELECT DISTINCT use: CAST(col_name AS NVARCHAR(MAX)), the same for join condition like CAST(p.proj_name AS NVARCHAR(MAX)) = CAST(Civilbill20.proj_name AS NVARCHAR(MAX))