Solved

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

Posted on 2014-09-17
6
249 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
[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
  • 4
  • 2
6 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 40329441
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 34

Author Comment

by:Mike Eghtebas
ID: 40329448
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
ID: 40329451
>>"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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40329457
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 34

Author Comment

by:Mike Eghtebas
ID: 40329462
Thank you.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40329468
& thank you. Cheers, Paul
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

737 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