postgres - sql server cte recursive

Error CTE: "Los tipos no coinciden entre el delimitador y la parte recursiva"

Question

Estoy ejecutando la siguiente declaración:

;WITH cte AS (
  SELECT 
    1 as rn, 
    ''name1'' as nm
  UNION ALL
  SELECT 
    rn + 1,
    nm = ''name'' + CAST((rn + 1) as varchar(255))
  FROM cte a WHERE rn < 10)
SELECT * 
FROM cte

... que termina con el error ...

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

¿Dónde estoy cometiendo el error?

Answer 1

Debes lanzar ambos campos nm

;with cte as
(
select  1 as rn, 
        CAST(''name1'' AS VARCHAR(255)) as nm
union all
select  rn+1,
        nm = CAST(''name'' + CAST((rn+1) as varchar(255)) AS VARCHAR(255))
from cte a where rn<10)
select * from cte

Answer 2

Exactamente lo que dice:

''name1'' tiene un tipo de datos diferente a ''name'' + CAST((rn+1) as varchar(255))

Prueba esto (no probado)

;with cte as
(
select 1 as rn, CAST(''name1'' as varchar(259)) as nm
union all
select rn+1,nm = ''name'' + CAST((rn+1) as varchar(255))
from cte a where rn<10)
select * from cte

Básicamente, debes asegurarte de que la longitud coincida también. Para el bit recursivo, puede que tenga que usar CAST(''name'' AS varchar(4)) si falla nuevamente

Answer 3

Para mí el problema estaba en colación diferente.

Solo esto me ayudó:

;WITH cte AS (
  SELECT 
    1 AS rn, 
    CAST(''name1'' AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS nm
  UNION ALL
  SELECT 
    rn + 1,
    nm = CAST(''name'' + CAST((rn + 1) AS NVARCHAR(255)) AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT
  FROM cte a WHERE rn < 10)
SELECT * 
FROM cte;

Espero que pueda ayudar a alguien más.

Answer 4

;with cte as
(
select 1 as rn, ''name'' + CAST(1 as varchar(255)) as nm
union all
select rn+1,nm = ''name'' + CAST((rn+1) as varchar(255))
from cte a where rn<10)
select * from cte

Answer 5

;with tmp1(NewsId,DataItem ,HeaderText)
 as
  (

    select NewsId, LEFT(HeaderText, CHARINDEX('','',HeaderText+'','')-1),
    STUFF(HeaderText, 1, CHARINDEX('','',HeaderText+'',''), '''') 
    from Currentnews

    union all

    select NewsId, LEFT(HeaderText, CHARINDEX('','',HeaderText+'','')-1),
    STUFF(HeaderText, 1, CHARINDEX('','',HeaderText+'',''), '''')
    from tmp1
    where HeaderText > ''''

   )

   select NewsId, DataItem
   from tmp1
   order by NewsId