Having problems with text datatype!

Hi,

pls can anyone help me to solve the error generated by this query,

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spAP_PS_VENDOR_CONVER]

AS

SET NOCOUNT ON

SELECT UPPER(SETID) AS SETID

,UPPER(VENDOR_ID)AS VENDOR_ID

,CONVER_DT

,CONVER_SEQ_NUM

,CNTCT_SEQ_NUM

,UPPER(CONVER_TOPIC) AS CONVER_TOPIC

,UPPER(OPRID)AS OPRID

,REVIEW_DAYS

,REVIEW_DATE

,REVIEW_NEXT_DATE

,UPPER(KEYWORD1) AS KEYWORD1

,UPPER(KEYWORD2) AS KEYWORD2

,UPPER(KEYWORD3) AS KEYWORD3

,CAST(ISNULL(DESCRLONG,'') AS VARCHAR(200)) AS DESCRLONG

,PROCESS_INSTANCE

,MAX(EY_SF_UPDATE_DTTM) AS EY_SF_UPDATE_DTTM

,PROCESS_DTTM

,CREATED_DTTM

,UPPER(EY_SF_ACTN_FLG) AS EY_SF_ACTN_FLG

,UPPER(EY_SF_STATUS) AS EY_SF_STATUS

FROM Metastorm.dbo.AP_PS_VENDOR_CONVER

WHERE EY_SF_STATUS='N'

GROUP BY SETID,VENDOR_ID,CONVER_DT,CONVER_SEQ_NUM,CNTCT_SEQ_NUM,CONVER_TOPIC,OPRID

,REVIEW_DAYS,REVIEW_DATE,REVIEW_NEXT_DATE,KEYWORD1,KEYWORD2,KEYWORD3,DESCRLONG

,PROCESS_INSTANCE,PROCESS_DTTM,CREATED_DTTM,EY_SF_ACTN_FLG

,EY_SF_STATUS

SET NOCOUNT OFF

Msg 306, Level 16, State 2, Procedure spAP_PS_VENDOR_CONVER, Line 4

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Regards,

Sg

sorry forgot to tell that the descrlong is a text column

Regards,

sg

|||

You couldn't use text, ntext, image datatype in group by.

Try use following statement in group by:

Code Snippet

GROUP BY CAST(ISNULL(DESCRLONG,'') AS VARCHAR(200)), .....

|||

Hi Konstantin,

Thanks a lot.

Regards,

Sg