Solved

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

Posted on 2014-09-17
6
243 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
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 33

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 33

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

813 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

14 Experts available now in Live!

Get 1:1 Help Now