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