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
