error in code "the text, ntext etc"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

error in code "the text, ntext etc"

Author

Topic

jewel
Starting Member

18 Posts

Posted - 2004-08-08 : 20:44:54

hey all

I have the following query and get this error:

"the text, ntext and image data types cannot be compared or sorted, except when using IS NULL or Like operator"

Now I think it maybe to do with the Call_Comments field??? but I'm not sure! as the query works without that field.

Can someone guide me here please

SELECT dbo.Call.CallNumber, dbo.Call.FirstName, dbo.Call.LastName, dbo.Call.Phone, dbo.Call.CallSubject1, dbo.Call.CallSubject2, dbo.Call.CallSubject3,
dbo.Call.LogDatTim, dbo.Call_Comments.Comments
FROM dbo.Call INNER JOIN
dbo.Call_Comments ON dbo.Call.CallNumber = dbo.Call_Comments.CallNumber
WHERE (dbo.Call.CallSubject1 = 'Hardware Install') AND (dbo.Call.CallSubject2 = 'Printer') AND (dbo.Call.CallSubject3 = 'Lan Printer') OR
(dbo.Call.CallSubject1 = 'Hardware Purchase') AND (dbo.Call.CallSubject2 = 'Printer(ex - lease') AND (dbo.Call.CallSubject3 = 'Local Printer') OR
(dbo.Call.CallSubject3 = 'New LAN Printer') OR
(dbo.Call.CallSubject3 = 'New Local Printer')
GROUP BY dbo.Call.CallNumber, dbo.Call.FirstName, dbo.Call.LastName, dbo.Call.Phone, dbo.Call.CallSubject1, dbo.Call.CallSubject2, dbo.Call.CallSubject3,
dbo.Call.LogDatTim, dbo.Call_Comments.Comments

thanks
jewel

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-08 : 21:01:08

Why are you doing a GROUP BY when you're not using any aggregates (COUNT, MAX etc)?

SQL doesn't like grouping on text fields (which I assume the Call_Comments field is).

If you can't re-write to get rid of the grouping on the comments field, you'll need to do a convert:
CONVERT(varchar, dbo.Call_Comments.Comments)

oh yeah - you might want to use table aliases. It makes the code much easier to read

jewel
Starting Member

18 Posts

Posted - 2004-08-08 : 23:01:30

thanks Timmy

I had added the GROUP BY on advice from someone else, obviously not really needed then as you are right it was not necessary. So I have changed it to an ORDER by for one field.

so my new code now reads:
SELECT dbo.Call.CallNumber, dbo.Call.FirstName, dbo.Call.LastName, dbo.Call.Phone, dbo.Call.CallSubject1, dbo.Call.CallSubject2, dbo.Call.CallSubject3,
dbo.Call.LogDatTim, dbo.Call_Comments.Comments
FROM dbo.Call INNER JOIN
dbo.Call_Comments ON dbo.Call.CallNumber = dbo.Call_Comments.CallNumber
WHERE (dbo.Call.CallSubject1 = 'Hardware Install') AND (dbo.Call.CallSubject2 = 'Printer') AND (dbo.Call.CallSubject3 = 'Lan Printer') OR
(dbo.Call.CallSubject1 = 'Hardware Purchase') AND (dbo.Call.CallSubject2 = 'Printer(ex - lease') AND (dbo.Call.CallSubject3 = 'Local Printer') OR
(dbo.Call.CallSubject3 = 'New LAN Printer') OR
(dbo.Call.CallSubject3 = 'New Local Printer')
ORDER BY dbo.Call.CallSubject1

Ok now another prob I need to add the following: (Call.LogDatTim >= '2003-06-01 00:00:00') AND (Call.LogDatTim <= '2003-06-30 00:00:00'

Presume I would add this into the WHERE clause - I have tried numerous ways with either AND or OR but don't get anywhere.

also what did you mean by "you might want to use table aliases. It makes the code much easier to read". I have come from an access background self-taught mainly so am struggling a little, so appreciate any help you can give. Is there a site with sql code examples that you know of??
thanks
Dianne

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-08 : 23:15:39

You can use the BETWEEN keyword for this:

WHERE Call.LogDatTim BETWEEN '2003-06-01 00:00:00' AND '2003-06-30 00:00:00'

To add it to your WHERE clause, the most reliable way is to bracket all the other conditions:

 WHERE (all your other clauses} AND {date clause)

You should probably lay out your WHERE clauses a bit better so you can see what's going on. Also, you can replace:

(dbo.Call.CallSubject3 = 'Local Printer') OR (dbo.Call.CallSubject3 = 'New LAN Printer') OR (dbo.Call.CallSubject3 = 'New Local Printer')

With this:

(dbo.Call.CallSubject3 IN ('Local Printer', 'New LAN Printer', 'New Local Printer')

Makes it much easier to read.
Table aliases are a sort of shorthand to allow you to make your code easier to read:


SELECT C.CallNumber, C.FirstName, C.LastName, C.Phone, C.CallSubject1,
       C.CallSubject2, C.CallSubject3, C.LogDatTim, CC.Comments
FROM dbo.Call C INNER JOIN dbo.Call_Comments CC ON C.CallNumber = CC.CallNumber
WHERE (C.CallSubject1 = 'Hardware Install') AND (C.CallSubject2 = 'Printer') AND (C.CallSubject3 = 'Lan Printer') OR
(C.CallSubject1 = 'Hardware Purchase') AND (C.CallSubject2 = 'Printer(ex - lease') AND (C.CallSubject3 = 'Local Printer') OR
(C.CallSubject3 = 'New LAN Printer') OR
(C.CallSubject3 = 'New Local Printer')
ORDER BY C.CallSubject1

You might find it beneficial to get a book on SQL basics - all these sorts of things would be covered.

Tim

jewel
Starting Member

18 Posts

Posted - 2004-08-08 : 23:58:32

ok thanks Timmy nearly there!

I have now changed it as below:

SELECT C.Call.CallNumber, C.Call.FirstName, C.Call.LastName, C.Call.Phone, C.Call.CallSubject1, C.Call.CallSubject2, C.Call.CallSubject3, C.Call.LogDatTim, C.Call_Comments.Comments
FROM dbo.Call INNER JOIN
dbo.Call_Comments ON C.Call.CallNumber = C.Call_Comments.CallNumber
WHERE (C.Call.CallSubject1 IN ('Hardware Install', 'Hardware Purchase') OR(C.Call.CallSubject2 IN ('Printer', 'Printer(ex-lease') OR
(C.Call.CallSubject3 IN ('Lan Printer', 'Local Printer', 'New LAN Printer', 'New Local Printer ')) AND C.Call.LogDatTim BETWEEN '2003-06-01 00:00:00' AND '2003-06-30 00:00:00'
ORDER BY C.Call.CallSubject1

but get "incorrect syntax near the keyword ORDER??

so still unable to complete

jewel

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-09 : 00:15:06

You're missing a ) somewhere.
Try running the query without any WHERE clauses and then gradually add them, running the query after each addition to ensure that your syntax is correct and the filtering is working as you expect.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-09 : 03:41:06

i'm guessing this is giving you an error
OR(C.Call.CallSubject2 IN ('Printer', 'Printer(ex-lease')

i thik it should be
OR(C.Call.CallSubject2 IN ('Printer', 'Printer(ex-lease)')

Go with the flow & have fun! Else fight the flow :)

jewel
Starting Member

18 Posts

Posted - 2004-08-09 : 20:06:13

thankyou all for your help

This is the result of my work and all is fine

SELECT C.Call.CallNumber, C.Call.FirstName, C.Call.LastName, C.Call.Phone, C.Call.CallSubject1, C.Call.CallSubject2, C.Call.CallSubject3, C.Call.LogDatTim, C.Call_Comments.Comments
FROM dbo.Call INNER JOIN dbo.Call_Comments ON dbo.Call.CallNumber = dbo.Call_Comments.CallNumber
WHERE (C.Call.CallSubject1 IN ('Hardware Install', 'Hardware Purchase')) AND (C.Call.CallSubject2 IN ('Printer', 'Printer(ex - lease')) AND (C.Call.CallSubject3 IN ('Lan Printer', 'Local Printer', 'New LAN Printer', 'New Local Printer')) AND (C.Call.LogDatTim BETWEEN '2003-06-01 00:00:00' AND '2003-06-30 00:00:00')

so thanks all
jewel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources