数据类型的运算符无效。运算符等于add，类型等于hierarchyid。 [英] Invalid operator for data type. Operator equals add, type equals hierarchyid.

问题描述

我正在尝试使用以下程序转换我的层次结构：

  DECLARE   @ FilterId   NVARCHAR （MAX） = '  CUSTOMER_IFRS' 
  DECLARE   @ unid   as   NVARCHAR （MAX） = '  1410' 
  DECLARE   @ id   HIERARCHID  
  SELECT   @ id  = HID  FROM  dbo.HIERARCHIES  WHERE  FilterId = @ FilterId 和 id =  @ unid ; 
 
 
  DECLARE   @ SQL   NVARCHAR （MAX）= '  SELECT OFSA_ID为' + @ FilterId + ' ，sName AS LEAF_DESCRIPTION' 
  DECLARE   @ ProductMaxLevel   as   NVARCHAR （MAX） 
  SELECT   @ ProductMaxLevel  = MAX（HID_Level） FROM  HIERARCHIES  WHERE  FilterId =  @ FilterId  
 
 
  DECLARE   @availableLevels   NVARCHAR （MAX） 
  DECLARE  LevelHierarchy  CURSOR   FOR  
  SELECT   DISTINCT  HID_Level  FROM  HIERARCHIES  WITH （< span class =code-keyword> NOLOCK ） WHERE  FilterId = '  CUSTOMER_IFRS'  order   by   1   asc ; 
  OPEN  LevelHierarchy; 
  FETCH  NEXT  FROM  LevelHierarchy  INTO   @availableLevels ; 
  WHILE   @availableLevels < =  @ ProductMaxLevel -1  
  BEGIN  
 
 
  SET   @ SQL  =  @ SQL  + '  
，（SELECT a.Ofsa_id FROM dbo.HIERARCHIES a其中a.FilterId =''' + @ FilterId + ' ''和' + @ id + '  .IsDescendantOf（a.HID）= 1 and a.Tree_Level =' + @availableLevels + ' ） AS LEVEL_0' + @availableLevels + '  _ ID，
（SELECT a.sName FROM dbo.HIERARCHIES a其中a.FilterId =' '' + @ FilterId + ' ''和' + @ id + ' 。IsDescendantOf（a.HID）= 1 and a.Tree_Level =' + @availableLevels + ' ）AS LEVEL_0' + @availableLevels + '  _ DESC' 
 
 
   -    PRINT @SQL  
  PRINT   @availableLevels  
 
 
 
 
  FETCH  NEXT  FROM  LevelHierarchy  INTO   @availableLevels ; 
  END ; 
  CLOSE  LevelHierarchy; 
  DEALLOCATE  LevelHierarchy; 
 
 
 
 
  SET   @ SQL  = @ SQL + '  FROM HIERARCHIES，其中FilterId =''' + @ FilterId + ' ''和id =' + @ unid + ' ' 
   -    EXECUTE sp_executesql @SQL  
  SELECT   @ SQL  

不幸的是，我收到了错误

 消息  403 ，等级 16 ，状态 1 ，行 22  
数据类型的运算符无效。运算符等于 add ，键入equals hierarchyid。

我认为错误是与我试图将@id参数传递给SQL语句的部分相关（''+ @ id +''。IsDescendantOf（a.HID）= 1）



如果有人可以帮助我解决问题。

任何建议/帮助/代码corecction将非常感谢。

谢谢

A

解决方案

通常，在连接两列Text数据类型时会出现上述错误。

详情请参见：< a href =http://sqlerrormessages.blogspot.in/2009/08/invalid-operator-for-data-type-operator.html>数据类型的运算符无效。运算符等于add，类型等于文本。 [ ^ ]



如上所述，要解决此错误，您需要将TEXT列转换为在连接列之前首先是VARCHAR。示例：

  SELECT  [StudentID]，
 CAST（CAST（[CommentsOnTeacher]  AS   VARCHAR （ 8000 ））+ 
 CAST（[CommentsOnSubject]  AS   VARCHAR （ 8000 ）） AS   TEXT ）
  AS  [AllComments] 
  FROM  [dbo]。[StudentComments] 
 

Hi,
i''m trying to transform my hierarchy using procedure as follows:

DECLARE @FilterId NVARCHAR(MAX) = 'CUSTOMER_IFRS'
DECLARE @unid as NVARCHAR(MAX) = '1410'
    DECLARE @id HIERARCHYID
        SELECT @id = HID FROM dbo.HIERARCHIES WHERE  FilterId =@FilterId  and id = @unid;


DECLARE @SQL NVARCHAR(MAX) = 'SELECT OFSA_ID as '+@FilterId+', sName AS LEAF_DESCRIPTION'
DECLARE @ProductMaxLevel as NVARCHAR(MAX)
SELECT @ProductMaxLevel = MAX(HID_Level)  FROM HIERARCHIES WHERE FilterId = @FilterId


DECLARE @usableLevels NVARCHAR(MAX)
DECLARE LevelHierarchy CURSOR FOR
    SELECT DISTINCT HID_Level FROM HIERARCHIES WITH (NOLOCK) WHERE FilterId = 'CUSTOMER_IFRS'  order by 1 asc;
            OPEN LevelHierarchy;
                FETCH NEXT FROM LevelHierarchy INTO @usableLevels;
                    WHILE @usableLevels <= @ProductMaxLevel-1
                        BEGIN


        SET @SQL = @SQL +'
        ,(SELECT a.Ofsa_id FROM  dbo.HIERARCHIES a  where  a.FilterId ='''+@FilterId+''' and  '+@id+'.IsDescendantOf(a.HID) = 1 and a.Tree_Level = '+@usableLevels+') AS LEVEL_0'+@usableLevels+'_ID,
         (SELECT a.sName FROM  dbo.HIERARCHIES a  where  a.FilterId ='''+@FilterId+''' and  '+@id+'.IsDescendantOf(a.HID) = 1 and a.Tree_Level = '+@usableLevels+') AS LEVEL_0'+@usableLevels+'_DESC'


                                --PRINT @SQL
                                PRINT @usableLevels




                                FETCH NEXT FROM LevelHierarchy INTO @usableLevels;
                    END;
                    CLOSE LevelHierarchy;
                    DEALLOCATE LevelHierarchy;




        SET @SQL =@SQL +' FROM HIERARCHIES Where FilterId ='''+@FilterId+''' and id = '+@unid+''
        --EXECUTE sp_executesql @SQL
        SELECT @SQL

Unfortunatelly, i''m getting error

Msg 403, Level 16, State 1, Line 22
Invalid operator for data type. Operator equals add, type equals hierarchyid.

I think the error is related to part where I''m trying to pass @id parameter into SQL Statement (''+@id+''.IsDescendantOf(a.HID) = 1)

If anyone can help me in order to to solve the problem.
Any advice/help/code corecction would be very much appreciated.
Thanks
A

解决方案

Generally, one get the above error when you concatenate two columns of Text datatype.
See for details here: Invalid operator for data type. Operator equals add, type equals text.[^]

As mentioned there, to work around this error you need to CAST the TEXT column into VARCHAR first before concatenating the columns. Sample:

SELECT [StudentID],
CAST(CAST([CommentsOnTeacher] AS VARCHAR(8000)) +
CAST([CommentsOnSubject] AS VARCHAR(8000)) AS TEXT)
AS [AllComments]
FROM [dbo].[StudentComments]

这篇关于数据类型的运算符无效。运算符等于add，类型等于hierarchyid。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！