1

Topic: Error in the recursive request

I ask the help.
There is a table

DECLARE @p TABLE (id INT, name NCHAR (100), obj_id CHAR (10), parent CHAR (10))

In it the data

INSERT @p SELECT 0, ' Ivanov ', ' IVA ', ' 0 '/*big boss*/
INSERT @p SELECT 1, ' Petrov ', ' PETR ', ' IVA '/*Podchinen Ivanovu*/
INSERT @p SELECT 2, ' Vasechkin ', ' VAS','IVA '/*Podchinen Ivanovu*/
INSERT @p SELECT 3, ' Sidorov ', ' CID ', ' PETR '/*Podchinen Petrovu*/
INSERT @p SELECT 4, ' Pupkin ', ' PUP ', ' PETR '/*Podchinen Petrovu*/
INSERT @p SELECT 5, ' Gadjukov ', ' GAD ', ' PUP '/*Podchinen Pupkinu*/
INSERT @p SELECT 6, ' Cats ', ' KOT ', ' GAD '/*Podchinen Gadjukovu*/
INSERT @p SELECT 7, ' Nepojmikto ', ' NONAME ', ' KOT '/*Podchinen Kotovu*/

I want to construct a submission tree that on an output to have something of type
;
Ivanov, Ivanov
Petrov, Ivanov/Petrov
, Ivanov/Vasechkin
Sidorov, Ivanov/Petrov/Sidorov
Pupkin, Ivanov/Petrov/Pupkin
, Ivanov/Petrov/Pupkin/Gadjukov
Cats, Ivanov/Petrov/Pupkin/Gadjukov/Kotov
,/////
Somehow so:

with tree (id_t, name_t, level_t, path_t)
as (select id, name, 0, name
from @p
where parent = ' 0'
union all
select p.id,
p.name,
tree.level_t + 1,
tree.name_t + ' / ' + p.name
from @p p
inner join tree on tree.id_t = p.parent)

I catch an exception
Types do not match between the anchor and the recursive part in column "path_t" of recursive query "tree".
What types do not coincide?

2

Re: Error in the recursive request

kesean;
Well, in the same place it is written in column "path_t".
In a case with recursive cte such error will be produced including if in an anchor part at a column type, for example, varchar (100), and in  already varchar (200).
It is necessary to result in one type explicitly.

with tree (id_t, name_t, level_t, path_t)
as (select id, name, 0, cast (name as varchar (max))
from @p
where parent = ' 0'
union all
select p.id,
p.name,
tree.level_t + 1,
cast (tree.name_t + ' / ' + p.name as varchar (max))
from @p p
inner join tree on tree.id_t = p.parent)

3

Re: Error in the recursive request

daw, thanks for the answer (I already to text resulted all smile)

4

Re: Error in the recursive request

kesean wrote:

(I already to text resulted all smile)

And here it in vain. At type text basically  restrictions, well and it plan to cancel eventually. So do not study in bad at once