Solved

distinct wth ntext not working

Posted on 2008-10-17
11
1,837 Views
Last Modified: 2012-05-05
hi
how can i get this working
e.Responsibilitiesud field is ntext and is causing this error ...

"Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable."
SELECT  DISTINCT
 
        EmployeeID      =       e.EmployeeID,
        Status          =       es.EmployeeStatus,
		Start_Date		=		e.EmployeeStartDate,
		Emp_Name        =       e.Surname + ', ' + e.KnownAs,
        LoginID         =       e.WindowsUserName,
        UserID          =       e.DisplayEmployeeID,
        Extension       =       e.ExtNoUD,
        FFWMobile       =       e.FFWMobileUD,
        Email           =       e.WorkEmail,
        ProfitCentre    =       ch.HierarchyLevel3,
        Department      =       ch.HierarchyLevel4,
        EliteNumber     =       ejwrks.Elitenumberud,
		Secretary       =       sec.EmployeeDescNoID,
        Location        =       e.LocationUD,
        Position        =       ej1.PostID,
		WorksFor1		=		EWF1.EmployeeDescNoID,
		WorksFor2		=		EWF2.EmployeeDescNoID,
		WorksFor3		=		EWF3.EmployeeDescNoID,
		WorksFor4		=		EWF4.EmployeeDescNoID,
		WorksFor5		=		EWF5.EmployeeDescNoID,
        WorksFor        =       EBoss.EmployeeDescNoID,
		AboutMe			=		e.AboutMeud,
		Responsibilites	=		e.Responsibilitiesud
 
FROM    [Cascade].dbo.Employee          e
 
JOIN    [Cascade].dbo.Employee_Status   es  
ON      e.EmployeeID            =       es.EmployeeID
 
LEFT OUTER JOIN 
        (
                select  t1.JobTitle, t1.EmployeeID, t1.PostID
                from    [Cascade].dbo.EmployeeJobs      t1
                join    (
                                select  employeeId, min(Sequence) as sequence
                                from    [Cascade].dbo.EmployeeJobs
                                group by employeeID
                        )                               t2 
                on      t1.employeeid   =       t2.employeeID   
                and     t1.Sequence     =       t2.Sequence
        )                               ej1  
ON				e.EmployeeID            =       ej1.EmployeeID
LEFT OUTER JOIN 
		[Cascade].dbo.Employee sec
ON      sec.EmployeeID          =       e.SecretaryNameUD 
 
JOIN    [Cascade].dbo.EmployeeJobs      ejwrks
ON      e.employeeid            =       ejwrks.employeeid
 
JOIN
        [Cascade].dbo.CompanyHierarchy   ch
ON      ejwrks.HierarchyNodeUD  =       ch.HierarchyNode
 
JOIN 
		[Cascade].dbo.Employee eBoss
ON		EBoss.EmployeeID		=		ejwrks.WorksForEmployeeID
 
LEFT OUTER JOIN
		[Cascade].dbo.Employee EWF1
ON		EWF1.EmployeeID			=		e.Manager1ud
 
LEFT OUTER JOIN
		[Cascade].dbo.Employee EWF2
ON		EWF2.EmployeeID			=		e.Manager2ud
 
LEFT OUTER JOIN
		[Cascade].dbo.Employee EWF3
ON		EWF3.EmployeeID			=		e.Manager3ud
 
LEFT OUTER JOIN
		[Cascade].dbo.Employee EWF4
ON		EWF4.EmployeeID			=		e.Manager4ud
 
LEFT OUTER JOIN
		[Cascade].dbo.Employee EWF5
ON		EWF5.EmployeeID			=		e.Manager5ud
 
WHERE   es.EmployeeStatus       =       'Current'  
AND		ch.[InUse?]				=		'Live'

Open in new window

0
Comment
Question by:mooriginal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 6

Expert Comment

by:mirzas
ID: 22739535
This is by design.
0
 

Author Comment

by:mooriginal
ID: 22739585
how do i get around it ?
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22739594
Use a different data type.

nvarchar should do the trick
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 6

Accepted Solution

by:
Ashok Kumar earned 350 total points
ID: 22739965
perhaps you can try to convert it to a different datatype in the select statement
 like Responsibilites      = convert(varchar(4000),e.Responsibilitiesud)
0
 

Author Comment

by:mooriginal
ID: 22757657
thought that would work but still getting the error message
0
 
LVL 6

Expert Comment

by:mirzas
ID: 22757683
Show the query you executed
0
 

Author Comment

by:mooriginal
ID: 22757883
ive tried this

select distinct(cast(Responsibilitiesud as varchar(50))) from employee
and it shows results so then tried

Responsibilites =       cast(e.Responsibilitiesud as varchar(50))

in the view below and still same error message
SELECT  DISTINCT
 
        EmployeeID      =       e.EmployeeID,
        Status          =       es.EmployeeStatus,
        Start_Date      =       e.EmployeeStartDate,
        Emp_Name        =       e.Surname + ', ' + e.KnownAs,
        LoginID         =       e.WindowsUserName,
        UserID          =       e.DisplayEmployeeID,
        Extension       =       e.ExtNoUD,
        FFWMobile       =       e.FFWMobileUD,
        Email           =       e.WorkEmail,
        ProfitCentre    =       ch.HierarchyLevel3,
        Department      =       ch.HierarchyLevel4,
        EliteNumber     =       ejwrks.Elitenumberud,
        Secretary       =       sec.EmployeeDescNoID,
        Location        =       e.LocationUD,
        Position        =       ej1.PostID,
        WorksFor1       =       EWF1.EmployeeDescNoID,
        WorksFor2       =       EWF2.EmployeeDescNoID,
        WorksFor3       =       EWF3.EmployeeDescNoID,
        WorksFor4       =       EWF4.EmployeeDescNoID,
        WorksFor5       =       EWF5.EmployeeDescNoID,
        WorksFor        =       EBoss.EmployeeDescNoID,
        AboutMe         =       e.AboutMeud,
        Responsibilites =       cast(e.Responsibilitiesud as varchar(50))
--Responsibilites = convert(varchar(4000),e.Responsibilitiesud)
 
 
 
FROM    [Cascade].dbo.Employee          e
 
JOIN    [Cascade].dbo.Employee_Status   es  
ON      e.EmployeeID    =       es.EmployeeID
 
LEFT OUTER JOIN 
        (
                select  t1.JobTitle, t1.EmployeeID, t1.PostID
                from    [Cascade].dbo.EmployeeJobs      t1
                join    (
                                select  employeeId, min(Sequence) as sequence
                                from    [Cascade].dbo.EmployeeJobs
                                group by employeeID
                        )                               t2 
                on      t1.employeeid   =       t2.employeeID   
                and     t1.Sequence     =       t2.Sequence
        )                               ej1  
ON                      e.EmployeeID            =       ej1.EmployeeID
LEFT OUTER JOIN 
        [Cascade].dbo.Employee sec
ON      sec.EmployeeID  =       e.SecretaryNameUD 
 
JOIN    [Cascade].dbo.EmployeeJobs      ejwrks
ON      e.employeeid    =       ejwrks.employeeid
 
JOIN
        [Cascade].dbo.CompanyHierarchy   ch
ON      ejwrks.HierarchyNodeUD  =   ch.HierarchyNode
 
JOIN 
        [Cascade].dbo.Employee eBoss
ON      EBoss.EmployeeID =               ejwrks.WorksForEmployeeID
 
LEFT OUTER JOIN
        [Cascade].dbo.Employee EWF1
ON      EWF1.EmployeeID  =               e.Manager1ud
 
LEFT OUTER JOIN
                [Cascade].dbo.Employee EWF2
ON              EWF2.EmployeeID                 =               e.Manager2ud
 
LEFT OUTER JOIN
                [Cascade].dbo.Employee EWF3
ON              EWF3.EmployeeID                 =               e.Manager3ud
 
LEFT OUTER JOIN
                [Cascade].dbo.Employee EWF4
ON              EWF4.EmployeeID                 =               e.Manager4ud
 
LEFT OUTER JOIN
                [Cascade].dbo.Employee EWF5
ON              EWF5.EmployeeID                 =               e.Manager5ud
 
WHERE   es.EmployeeStatus						=       'Current'  
AND     ch.[InUse?]								=		'Live'

Open in new window

0
 

Author Comment

by:mooriginal
ID: 22757898
and as you can see from sql above - also tried
Responsibilites      = convert(varchar(4000),e.Responsibilitiesud)

and same error message
0
 

Author Comment

by:mooriginal
ID: 22757999
The ntext data type cannot be selected as DISTINCT because it is not comparable.
0
 
LVL 6

Assisted Solution

by:mirzas
mirzas earned 150 total points
ID: 22758178
Can you show the table definition that shows column data types?

There must be another column that is ntext
0
 

Author Comment

by:mooriginal
ID: 22758539
yup you were right i was missing one ...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Check ALL SP in database make sure there are no errors 17 61
Where on a calculated field 1 31
RAISERROR WITH NOWAIT 2 25
SQL Calculation syntax based on a parameter 2 31
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question