Databases
StackOverflow2013
Postsdbo
POT-SQL CTE Error: Types don't match between the anchor and the recursive part
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL CTE Error: Types don't match between the anchor and the recursive part
    text
    copied!<p>I get the following error when I try to execute a particular recursive CTE:</p> <pre><code>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". </code></pre> <p>This is nonsense. Each field is explicitly cast to <code>VARCHAR(MAX)</code>. 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. </p> <p>This code will reproduce the error: </p> <pre><code>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 &gt; 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; </code></pre> <p>If you find it relevant, <code>SELECT left(@@VERSION, 70)</code> returns:</p> <pre><code>Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload