Solved

distinct wth ntext not working

Posted on 2008-10-17
11
1,832 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now