Help to Convert text field using SQL Code

I have my query listed below and I am gettin the following error. I know this is becasue I need to convert a text field in someway but I do not know how
other than the way I have can someone please help! I need to do this so I can put the Distinct Clause on my query.

Thanks!!



msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.


SELECT DISTINCT
pv.VISIT_NUMBER AS ENCOUNTER_NO,
pm2.MED_REC_NO,
a2.ACC_CATG AS CATEGORY,
a2.STATUS_FINAL AS IS_FINAL_CASE,
a2.ACCESSION_NO,
rcl.NAME AS FINDING_NAME,
CONVERT(VARCHAR(MAX),acr.FINDING_TEXT)AS FINDING_TEXT,
acr.FINDING,
COALESCE (CONVERT(text, acr.finding),acr.finding_text)AS RESULT_TEXT,
pr2.GEN_TITLE AS ASSIGNED_TO_TITLE,
pr2.FIRST_NAME AS ASSIGNED_TO_FIRST_NAME,
pr2.LAST_NAME AS ASSIGNED_TO_LAST_NAME,
pr2.MID_NAME AS ASSIGNED_TO_MID_NAME,
pn2.last_name,
pn2.first_name,
pn2.mid_name,
p2.home_street1,
p2.home_street2,
p2.home_city,
p2.home_state,
p2.home_postal_code,
CONVERT(nvarchar, P2.birth_date, 101) AS DOB,
p2.sex,
'N/A' AS Race,
p2.home_phone,
p2.emplr_name,
p2.emplr_phone,
rcl.NAME AS BLOCK_DIAGNOSIS,
sp.DESCRIPTION AS BLOCK_GROSS_DESCRIPTION,
acr.ACC_SPECIMEN_ID AS SPECIMEN_BLOCK_CODE,
ps.DESCRIPTION,
CONVERT(nvarchar, aps.completed_date, 101) AS CASE_STEP_COMPLETION_DATE,
rcl.name AS CASE_DIAGNOSIS_CLASSIFICATION,
at.name AS CASE_TYPE,
sp.description AS PRIMARY_SPEC_DESCRIPTION,
dtf.name AS REQUISITION_DATA_FIELD,
COALESCE (CONVERT(text, ad.val), ad.val_text) AS REQUISITION_DATA_FIELD_VALUE,
ac.billing_code AS CPT_CODES,
a2.primary_diag_finding AS CASE_PRIMARY_DIAGNOSIS,
'FINDING TEXT ' AS HPVDETECTED,/*found in Finding Text or Result Text*/
'N/A ' AS HORMONAL_EVALUATION,/*SELDOM IF EVER USED*/
'N/A ' AS ICD_9,/*ICD_9 TABLES ARE NOT POPULATED*/
'FINDING TEXT ' AS INFLAMMATION,/*REPORTED IN RESULTS TAB FINDING TEXT OR RESULT TEXT*/
'SEE CASE TYPE' AS LOCAL_ORDER_CODE,
sp.acc_id AS LOCAL_STANDARD_CODE,
sp.specimen_label AS ORDER_NAME,
'N/A' AS ORGANISMS,
'FINDING TEXT' AS OTHERLOCALRESULTDXCODES,
'FINDING TEXT' AS CYTOLOGIC_FINDINGS,
'FINDING TEXT' AS ADDENDUM,
'FINDING TEXT' AS MICROSCOPIC_DESC_1,
'FINDING' AS MICROSCOPIC_DESC_2,
'REF_BELOW_ASSIGNED_TO_FIELD' AS ASSIGNED_TO,
'REQ_DATA_FIELD' AS DATE_OF_LAST_PAP,
acr.finding_text AS RESULT_TEXT_CYTOLOGIC_DIAGNOSIS,
'DIGENE' AS HPV_TEST_MANUFACTURER,
CASE WHEN ad.acc_id IS NULL THEN 'N' ELSE 'Y' END AS req_data_field_has_data,
CASE
WHEN dtf.data_type = '0' THEN 'Yes/No With Explanation'
WHEN dtf.data_type = '1' THEN 'Text'
WHEN dtf.data_type = '2' THEN 'Integer'
WHEN dtf.data_type = '4' THEN 'Date/Time'
WHEN dtf.data_type = '6' THEN 'Text'
WHEN dtf.data_type = '8' THEN 'Heading'
WHEN dtf.data_type = '9' THEN 'Yes/No'
END AS requisition_data_field_type,
'N/A' AS LOINC_CODES,
'N/A' AS NOTE_TEXT_DICTATION,
'N/A' AS NPIN,
'N/A' AS PRIMARY_REFMD_PHONE,
dl2.code AS primary_refmd_location_dept,
dl2.name AS primary_refmd_location_name,
r2.first_name AS primary_refmd_first_name,
r2.gen_title AS primary_refmd_gen_title,
r2.last_name AS primary_refmd_last_name,
r2.mid_name AS primary_refmd_mid_name,
'SED MEDICAL LABRATORIES' AS LABCODE,
'SEE_CASE_PRIMARY_DIAGNOSIS' AS DIAGNOSIS

FROM dbo.accession_2 as a2

INNER JOIN dbo.acc_type as at
ON at.id=a2.acc_type_id

INNER JOIN DBO.ACC_CHARGES as ac
ON ac.ACC_ID=a2.id

INNER JOIN dbo.acc_results as acr
ON acr.acc_id = a2.id

INNER JOIN dbo.acc_specimen as sp
ON sp.id =a2.primary_specimen_id

lEFT JOIN dbo.acc_other_data as aod
ON aod.acc_id=sp.acc_id

INNER JOIN dbo.acc_process_step as aps
ON aps.id=a2.current_status_id

LEFT JOIN dbo.personnel_2 as pr2
ON pr2.id=aps.assigned_to_id

INNER JOIN dbo.acc_refmd as ar
ON ar.acc_id=a2.id

INNER JOIN dbo.delivery_location_2 as dl2
ON dl2.refmd_id=ar.refmd_id

INNER JOIN dbo.data_tmplt_field as dtf
ON dtf.tmplt_id=a2.data_tmplt_id

LEFT OUTER JOIN dbo.acc_other_data AS ad
ON ad.tmplt_id = dtf.tmplt_id AND ad.field_id = dtf.field_id

LEFT JOIN DBO.PATIENT_MRN_2 as pm2
ON pm2.id=a2.patient_mrn_id

INNER JOIN dbo.process_step as ps
ON ps.id=aps.step_id

LEFT JOIN dbo.patient_visit as pv
ON pv.patient_visit_id=a2.patient_visit_id

INNER JOIN dbo.refmd_2 as r2
ON r2.id=a2.refmd_id

LEFT JOIN dbo.results_classification as rcl
ON rcl.id=a2.results_classification_id
LEFT JOIN dbo.patient_2 AS p2
on pm2.id=p2.id
INNER JOIN dbo.patient_name_2 AS pn2
on p2.id=pn2.patient_id

WHERE aps.completed_date BETWEEN '01-JAN-11' AND '31-JAN-11'
AND a2.STATUS_FINAL ='Y'
AND ad.val_text is not null
AND pv.VISIT_NUMBER IS NOT NULL
AND a2.ACC_CATG IN ('G','N','S')