Solved

The text, ntext, and image data types cannot be compared...

Posted on 2014-09-17
6
240 Views
Last Modified: 2014-09-17
In the following query, I get  an error:

Msg 306, Level 16, State 2, Line 7
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

FYI,  [Description] is Text, not null
SELECT 
	s.Description AS Software
	, SUM(d.Quantity) AS TotalQuantity
FROM tblSoftware s 
INNER JOIN tblOrderDetails d
ON s.SoftwareID = d.SoftwareID 
GROUP BY s.Description;

Open in new window

When I replace s.Description with s.SoftwareID in two places, it runs okay.

Question: Is Description seen as memo field? And that is why I am getting this error?

Can I somehow convert s.Description to string to make it run?

If not, the other choice I see is dump the result to a temp table with SoftwareID and then update a blank desciption field in the temp table.

Thanks for your comment in advance.
0
Comment
Question by:Mike Eghtebas
  • 4
  • 2
6 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
Comment Utility
Is Description seen as memo field? And that is why I am getting this error?

YES!

that field is probbaly TEXT or NTEXT, and this part of the error message is the reason it will not work:

text, ntext, and image data types cannot be .... sorted

When attempting to use it in the GROUP BY clause you need to sort that field - which it cannot do.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Meanwhile I produced this rather lengthy solution what possibly could be handled easily:
-- dump the result in a tempTable ta with s.SoftwareID replacing s.Description
-- and then uptate ta to get ta.Description populated:
DECLARE @ta table (
	ID int
	, [Desc] varchar(50)
	, Quantity int)

INSERT INTO @ta (
	ID
	, Quantity)
SELECT 
	s.SoftwareID
	, SUM(d.Quantity) AS TotalQuantity
FROM tblSoftware s 
INNER JOIN tblOrderDetails d
ON s.SoftwareID = d.SoftwareID 
GROUP BY s.SoftwareID;
 


UPDATE
    @ta
SET
    t.[Desc] = s.Description
FROM
    @ta t
INNER JOIN
    tblSoftware s
ON 
    s.SoftwareID = t.ID

 Select * from @ta

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
Comment Utility
>>"If not, the other choice I see is dump the result to a temp table with SoftwareID and then update a blank description field in the temp table."

Well you might be able to do that, but why not just join back to the table instead?

SELECT
      od.description
    , q.TotalQuantity
FROM tblOrderDetails AS od
      INNER JOIN (
                  SELECT
                        s.SoftwareID
                      , SUM(d.Quantity) AS TotalQuantity
                  FROM tblSoftware s
                        INNER JOIN tblOrderDetails d
                                    ON s.SoftwareID = d.SoftwareID
                  GROUP BY
                        s.SoftwareID
            ) AS Q
                  ON od.SoftwareID = Q.SoftwareID
;

Open in new window

BUT I suspect you need more than just this.
If you do need more please provide some sample data PER TABLE (not the query result)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
By the way you need to look ahead on this issue:

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.
ntext, text, and image (Transact-SQL)
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Thank you.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
& thank you. Cheers, Paul
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

771 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

11 Experts available now in Live!

Get 1:1 Help Now