Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1849
  • Last Modified:

distinct wth ntext not working

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
mooriginal
Asked:
mooriginal
  • 6
  • 4
2 Solutions
 
mirzasCommented:
This is by design.
0
 
mooriginalAuthor Commented:
how do i get around it ?
0
 
mirzasCommented:
Use a different data type.

nvarchar should do the trick
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ashok KumarCommented:
perhaps you can try to convert it to a different datatype in the select statement
 like Responsibilites      = convert(varchar(4000),e.Responsibilitiesud)
0
 
mooriginalAuthor Commented:
thought that would work but still getting the error message
0
 
mirzasCommented:
Show the query you executed
0
 
mooriginalAuthor Commented:
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
 
mooriginalAuthor Commented:
and as you can see from sql above - also tried
Responsibilites      = convert(varchar(4000),e.Responsibilitiesud)

and same error message
0
 
mooriginalAuthor Commented:
The ntext data type cannot be selected as DISTINCT because it is not comparable.
0
 
mirzasCommented:
Can you show the table definition that shows column data types?

There must be another column that is ntext
0
 
mooriginalAuthor Commented:
yup you were right i was missing one ...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now