Concatenating strings in a CTE

Hi,

I am trying to create a concatenation of strings in a CTE, to show a "breadcrumb" path for a parent-child hierarchy. I get the error message,

Msg 240, Level 16, State 1, Line 3

Types don't match between the anchor and the recursive part in column "path_name" of recursive query "pc_hier_cte1".

Here are some SQL statements to study the situation. The first CTE merely computes the length of the "breadcrumb" string -- and it works. In the second CTE I try to perform the string concatenation, and that's where I get the error message.

Thanks for your help with this.

Dan

drop table #temp

drop table #pc_hier

create table #pc_hier

(

id int,

parent_id int,

name varchar(100)

)

insert into #pc_hier

select 1, 0, 'top level node' union all

select 2, 1, 'second level node 2' union all

select 3, 1, 'second level node 3' union all

select 4, 1, 'second level node 4' union all

select 20, 2, 'third level node 20' union all

select 21, 2, 'third level node 21' union all

select 22, 2, 'third level node 22' union all

select 30, 3, 'third level node 30' union all

select 31, 3, 'third level node 31' union all

select 32, 3, 'third level node 32' union all

select 40, 4, 'third level node 40' union all

select 41, 4, 'third level node 41' union all

select 42, 4, 'third level node 42'

;

with pc_hier_cte (id, parent_id, name, dist_from_parent, path_length) AS

(

select

ph.id,

ph.parent_id,

ph.name,

0 as dist_from_parent,

0 as path_length -- do not include top-level parent name in the path string

from #pc_hier ph

left outer join #pc_hier ph1

on ph.parent_id = ph1.id

where ph1.id is null -- never a parent

union all

select

ph.id,

ph.parent_id,

ph.name,

dist_from_parent + 1,

(phc.path_length + len(' > ' + ph.name)) as path_length

from #pc_hier ph

inner join pc_hier_cte phc

on ph.parent_id = phc.id

)

select

id,

parent_id,

name,

dist_from_parent,

path_length

into #temp

from pc_hier_cte

order by 4, 3, 1, 2

OPTION (MAXRECURSION 10);

select *

from #temp

order by 4,3,1,2

;

-- Here is the part that does not work, where I am trying to concatenate strings.

drop table #temp1

with pc_hier_cte1 (id, parent_id, name, dist_from_parent, path_length, path_name) AS

(

select

ph.id,

ph.parent_id,

ph.name,

0 as dist_from_parent,

0 as path_length, -- do not include top-level parent name in the path string

'' as path_name

from #pc_hier ph

left outer join #pc_hier ph1

on ph.parent_id = ph1.id

where ph1.id is null -- never a parent

union all

select

ph.id,

ph.parent_id,

ph.name,

dist_from_parent + 1,

(phc.path_length + len(' > ' + ph.name)) as path_length,

(phc.path_name + ' > ' + ph.name) as path_name

from #pc_hier ph

inner join pc_hier_cte1 phc

on ph.parent_id = phc.id

)

select

id,

parent_id,

name,

dist_from_parent,

path_length,

path_name

into #temp1

from pc_hier_cte1

order by 4, 3, 1, 2

OPTION (MAXRECURSION 10);

Hmmmm, by reading a post from today I solved this problem: I just needed to use CAST to define the value as a large VARCHAR. (I thank Kent Waldrop My07 for his post.)

Here is the fix.

drop table #temp1

with pc_hier_cte1 (id, parent_id, name, dist_from_parent, path_length, path_name) AS

(

select

ph.id,

ph.parent_id,

ph.name,

0 as dist_from_parent,

0 as path_length, -- do not include top-level parent name in the path string

cast('' as varchar(1000)) as path_name

from #pc_hier ph

left outer join #pc_hier ph1

on ph.parent_id = ph1.id

where ph1.id is null -- never a parent

union all

select

ph.id,

ph.parent_id,

ph.name,

dist_from_parent + 1,

(phc.path_length + len(' > ' + ph.name)) as path_length,

cast((phc.path_name + ' > ' + ph.name) as varchar(1000)) as path_name

from #pc_hier ph

inner join pc_hier_cte1 phc

on ph.parent_id = phc.id

)

select

id,

parent_id,

name,

dist_from_parent,

path_length,

path_name

into #temp1

from pc_hier_cte1

order by 4, 3, 1, 2

OPTION (MAXRECURSION 10);

select *

from #temp1

order by 4,3,1,2

;