T-SQL CTE错误：锚点和递归部分之间的类型不匹配 [英] T-SQL CTE Error: Types don't match between the anchor and the recursive part

问题描述

当我尝试执行特定的递归CTE时，出现以下错误：

I get the following error when I try to execute a particular recursive CTE:

Msg 240, Level 16, State 1, Line 8
Types don't match between the anchor and the recursive part in column "data_list" of recursive query "CTE".

这是胡说。每个字段都显式转换为 VARCHAR（MAX）。
请帮助我。在这里和其他地方，我已经阅读了许多有关此问题的答案，所有这些建议都建议您明确转换该列。我已经在执行此操作，但仍然会收到错误消息。

This is nonsense. Each field is explicitly cast to VARCHAR(MAX). Please help me. I've read many answers to this problem, here and elsewhere, all of which advise explicitly casting the column in question. I'm already doing this, and still get the error.

此代码将重现错误：

if object_id('tempdb..#tOwner') IS NOT NULL drop table #tOwner;
CREATE TABLE #tOwner(id int identity(1,1), email varchar(max) );
insert into #towner values ( cast('123@123.321'  as varchar(max)));
insert into #towner values ( cast('tsql rage'    as varchar(max)));
insert into #towner values ( cast('another@e.c'  as varchar(max)));
insert into #towner values ( cast('einstein.x.m' as varchar(max)));

;WITH data AS (
    SELECT DISTINCT convert(varchar(max), email) datapoint FROM #tOwner 
), CTE ( data_list, datapoint, length ) AS ( 
        SELECT convert(VARCHAR(max),            ''           ),convert(VARCHAR(max),    ''     ),       0
    UNION ALL
        SELECT convert(VARCHAR(max),d.datapoint+';'+data_list),convert(VARCHAR(max),d.datapoint), length + 1
        FROM CTE c CROSS JOIN data d WHERE d.datapoint > c.datapoint 
)
SELECT D.data_list
FROM ( 
    SELECT data_list, RANK() OVER ( PARTITION BY 1 ORDER BY length DESC ) 
    FROM CTE 
) D ( data_list, rank )
WHERE rank = 1 ;

drop table #tOwner;

如果您认为相关，请选择左键（@@ VERSION，70） 返回：

If you find it relevant, SELECT left(@@VERSION, 70) returns:

Microsoft SQL Server 2005 - 9.00.4053.00 (X64)   May 26 2009 14:13:01 

推荐答案

A会在我的原始帖子中的评论中找到关键-归类。我发布的查询在主数据库中也对我有用。

Will A's comment on my original post found the key - the collation. My posted query worked for me in the master database, too.

检查排序规则表明我走了正确的路。

Examining the collation suggested I was on the right track.

SELECT DATABASEPROPERTYEX('crm_mscrm', 'Collation') crmSQLCollation
crmSQLCollation
--------------------
Latin1_General_CI_AI
(1 row(s) affected)

SELECT DATABASEPROPERTYEX('master', 'Collation') masterSQLCollation
masterSQLCollation
----------------------------
SQL_Latin1_General_CP1_CI_AS
(1 row(s) affected)

后来有些疯狂的搜索，我得到了这段代码的怪异之处，

Some frenzied searching later, I had this monstrosity of code, which

1. 明确指定每列的排序规则，


2. 成功执行，并且


3. 返回预期结果

机智：

if object_id('tempdb..#tOwner') IS NOT NULL drop table #tOwner;
CREATE TABLE #tOwner(id int identity(1,1), email nvarchar(max) );
insert into #towner values ( cast('123@123.321'  as nvarchar(max)));
insert into #towner values ( cast('tsql rage'    as nvarchar(max)));
insert into #towner values ( cast('another@e.c'  as nvarchar(max)));
insert into #towner values ( cast('einstein.x.m' as nvarchar(max)));

;WITH data AS (
    SELECT DISTINCT convert(nvarchar(max), email) datapoint FROM #tOwner 
), CTE ( data_list, datapoint, length ) AS ( 
        SELECT convert(nvarchar(max),            ''           ) Collate SQL_Latin1_General_CP1_CI_AS,convert(nvarchar(max),    ''     ) Collate SQL_Latin1_General_CP1_CI_AS,       0
    UNION ALL
        SELECT convert(nvarchar(max),d.datapoint+';'+data_list) Collate SQL_Latin1_General_CP1_CI_AS,convert(nvarchar(max),d.datapoint) Collate SQL_Latin1_General_CP1_CI_AS, length + 1
        FROM CTE c CROSS JOIN data d WHERE d.datapoint > c.datapoint 
)
SELECT D.data_list
FROM ( 
    SELECT data_list, RANK() OVER ( PARTITION BY 1 ORDER BY length DESC ) 
    FROM CTE 
) D ( data_list, rank )
WHERE rank = 1 ;

if object_id('tempdb..#tOwner') IS NOT NULL drop table #tOwner;

期望在我的结果窗口中漂亮地坐着：

Sitting beautifully in my results window is the expected:

data_list
------------------------------------------------
tsql rage;einstein.x.m;another@e.c;123@123.321;

这篇关于T-SQL CTE错误：锚点和递归部分之间的类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！