Conditional Processing from a Common Table Expression (CTE)

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:

WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */

END
ELSE
BEGIN
/* Do something else here */

END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

WITH statement is a part of SELECT/INSERT/UPDATE/DELETE statement. As result you code doesn't work.

What processing do you need?

|||

What exactly you want to do on the /* Do Some processing here */. It might help you to give the rite solution.

You can end the CTE expression with INSERT/DELETE/UPDATE/SELECT. Othere than this 4 statement none of them are allowed.

|||

Thanks for the quick response.

All I need to do in each part of the IF ...ELSE..... is to perform a select, but which select statement to use depends on a field in the CTE. One select pulls data from a table in current database, whereas the other pulls data from a table on a linked server. The tables have similar but not exactly the same structures.

As I mentioned, I have what I need working using a cursor, I was just wondering if this were possible with a CTE, but based on what you and other repsondents have stated, it appears unlikely.

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

;