troubleshooting Question

A column that uses TEXT as the data type instead of varchar(max).

Avatar of skull52
skull52Flag for United States of America asked on
Microsoft SQL Server 2005
4 Comments1 Solution319 ViewsLast Modified:
I am using SQL 2005 and have a table the with a column (CMMTTEXT) that uses TEXT as the data type instead of varchar(max).  I am using a CASE statement and it works if I use IS NULL to replace the NULL in the column but throws “The data types text and varchar are incompatible in the equal to operator.” When  I add  the =’’ to replace it when it is blank. I don’t want to change it as I don’t know the results of doing so as it is a Microsoft Dynamics Great Plains table.  So I attempted to use Convert and Cast but still got the error. Any help would be greatly appreciated.

CASE WHEN SOP10106.CMMTTEXT IS NULL THEN convert(varchar(max),'No Info Supplied') WHEN SOP10106.CMMTTEXT = '' THEN convert(varchar(max),'No Info Supplied')  ELSE
 CAST(SOP10106.CMMTTEXT AS varchar(max))END AS CMMTTEXT
SELECT     V_ATIService.SOPTYPE, V_ATIService.SOPNUMBE, V_ATIService.DOCDATE, V_ATIService.BACHNUMB, V_ATIService.CUSTNMBR, 
                      V_ATIService.ShipToName, V_ATIService.CUSTNAME, V_ATIService.CNTCPRSN, V_ATIService.ADDRESS1, V_ATIService.ADDRESS2, 
                      V_ATIService.CITY, V_ATIService.STATE, V_ATIService.ZIPCODE, V_ATIService.PHNUMBR1, V_ATIService.FAXNUMBR, V_ATIService.PHNUMBR2, 
                      V_ATIService.CSTPONBR, V_ATIServiceLines.ITEMNMBR, V_ATIServiceLines.ITEMDESC, CASE WHEN SOP10201.SERLTNUM IS NULL THEN 'NONE' ELSE 
SOP10201.SERLTNUM END AS SERLTNUM, CASE WHEN SOP10106.CMMTTEXT IS NULL THEN convert(varchar(max),'No Info Supplied') WHEN SOP10106.CMMTTEXT = '' THEN convert(varchar(max),'No Info Supplied')  ELSE 
 CAST(SOP10106.CMMTTEXT AS varchar(max))END AS CMMTTEXT 
FROM         V_ATIService INNER JOIN
                      V_ATIServiceLines ON V_ATIService.SOPTYPE = V_ATIServiceLines.SOPTYPE AND 
                      V_ATIService.SOPNUMBE = V_ATIServiceLines.SOPNUMBE LEFT OUTER JOIN
                      SOP10106 ON V_ATIService.SOPNUMBE = SOP10106.SOPNUMBE AND V_ATIService.SOPTYPE = SOP10106.SOPTYPE LEFT OUTER JOIN
                      SOP10201 ON V_ATIServiceLines.SOPTYPE = SOP10201.SOPTYPE AND V_ATIServiceLines.SOPNUMBE = SOP10201.SOPNUMBE
WHERE     (V_ATIService.CUSTNMBR = 'ATI SERVICE')
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros