CODEWITHSUNDEEP
sqlsql-server
MasterOfStupidQuestions
MasterOfStupidQuestions

Reputation: 925

text, ntext, and image data types cannot be compared or sorted

SQL Server to show if field1 is null then field2, if field1 and field2 are null then field3, if field1, field2, field3 are null then field4 if all 4 fields are null then NULL How would this be displayed in SQL Server? I am assuming maybe a case statement and do case when etc but I am lost on this.

EDIT -- I tried running a straight COALESCE function like suggested and I get an error The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Select
Count(SoldNum),
Coalesce(Store1, Store2, Store3, Store4) As Store_Item_Sold_From
ItemSoldBy
ItemSold
FROM salesDatabase
Where Sold Is not null
Group By ItemSoldBy, ItemSold, Coalesce(Store1, Store2, Store3, Store4)

Upvotes: 1

Views: 7401

Answers (5)

Anon
Anon

Reputation: 10918

Don't use text for this. Use varchar

The problem is not because of COALESCE(), it's because of grouping by a text (or image) field.

DECLARE @t TABLE (a text)
SELECT a FROM @t GROUP BY a

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

Text/ntext/image types are not sortable like numbers or varchar. If you have a bunch of books, you wouldn't put "Fahrenheit 451" next to "The Bell Jar", because their first words are "It was a p..." and "It was a q...". You wouldn't sort images by the color of the first pixel. Use a text/image field to hold the contents of a book/picture and a varchar field to hold the title. Then sort by the title.

Upvotes: 1

Maverick
Maverick

Reputation: 1185

As far as I know COALESCE will not work for this case because the sort is in ntext or text field and they might be null or not null.

I think what you can do is CASE Statement like this:

SELECT  CASE WHEN CONVERT(NVARCHAR(MAX),Field1) IS NULL THEN CONVERT(NVARCHAR(MAX),Field2)
WHEN CONVERT(NVARCHAR(MAX),Field1) IS NULL AND CONVERT(NVARCHAR(MAX),Field2) THEN CONVERT(NVARCHAR(MAX),Field3)
WHEN CONVERT(NVARCHAR(MAX),Field1) IS NULL AND CONVERT(NVARCHAR(MAX),Field2) AND CONVERT(NVARCHAR(MAX),Field3) THEN CONVERT(NVARCHAR(MAX),Field4)
WHEN CONVERT(NVARCHAR(MAX),Field4) IS NULL THEN Field4 END
    FROM YOURTABLE

NOTE: You can use datatype depending on your case and you can change conditions AND, OR operators as well

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28423

COALESCE Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

SELECT COALESCE(field1, field2, field3, field4) FROM yourtable

COALESCE

Or You can use CASE Statement

SELECT CASE WHEN field1 IS NULL THEN Field2
            WHEN Field1 IS NULL AND Field2 IS NULL Then Field3
            WHEN Field1 IS NULL AND Field2 IS NULL AND Field3 IS NULL Then Field4
            ELSE 'NIL'
            END

Upvotes: 0

Liath
Liath

Reputation: 10191

You're looking for the coalesce function

SELECT COALESCE(field1, field2, field3, field4) FROM Table

If all four fields are null then your final result will be null. Unless of course you were to add a default:

SELECT COALESCE(field1, field2, field3, field4, 'Not Found') FROM Table

Upvotes: 1

Steven Wexler
Steven Wexler

Reputation: 17329

You're looking for the COALESCE operator

SELECT COALESCE(field1, field2, field3, field4)

Upvotes: 4

Related Questions