SQL Server的附近有语法错误（'在递归查询

Question

我试图将IBM DB2查询转换为SQL Server查询。我不是很熟悉与AS结构。

DB2查询（工作）是：

with ZoneList (id, name, parent_name, parent_id, level) as 
(select id, name, '', parent_id, 1 as level 
from products.zones where id = 1 
union all 
select z.id, z.name, l.name, z.parent_id, level + 1 
from products.zones z, ZoneList l 
where z.parent_id = l.id) 
select id, name || ' (' || parent_name || ')' as description 
from ZoneList 
where level = 4 
order by ZoneList.name 

和我的SQL Server版本是：

with ZoneList (id, name, cast((parent_name) as varchar(45)), parent_id, 
level) as 
(select id, name, cast(('') as varchar(45)), parent_id, 1 as level 
from products.zones where id = 1 
union all 
select z.id, z.name, cast((l.name) as varchar(45)), z.parent_id, level + 1 
from products.zones z, ZoneList l 
where z.parent_id = l.id) 
select id, name + ' (' + parent_name + ')' as description 
from ZoneList 
where level = 4 
order by ZoneList.name 

我为了避免不兼容类型的错误增加了石膏，但现在我得到语法错误附近“（”

Answer 1

试试这个：

;with ZoneList (id, name, parent_name, parent_id, level) 
as (
    select id 
     , name 
     , cast('' as varchar(45)) 
     , parent_id 
     , 1 as level 
    from products.zones 
    where id = 1 

    union all 

    select z.id 
     , z.name 
     , cast(l.name as varchar(45)) 
     , z.parent_id 
     , level + 1 
    from products.zones z 
     inner join ZoneList l 
      on z.parent_id = l.id 
    ) 
select id 
    , name + ' (' + parent_name + ')' as description 
from ZoneList 
where level = 4 
order by ZoneList.name 

你不需要任何显式转换在第一线，因为在第一行，你枚举CTE的列，你没有定义自己的数据类型或大小等。

你甚至可能完全在CTE的顶部在你的CTE删除该列枚举，只要你的别名每一列，如：

;with ZoneList 
as (
    select id 
     , name 
     , cast('' as varchar(45)) as [parent_name] 
     , parent_id 
     , 1 as level 
    from products.zones 
    where id = 1 

    union all 

    select z.id 
     , z.name 
     , cast(l.name as varchar(45)) as [parent_name] 
     , z.parent_id 
     , level + 1 
    from products.zones z 
     inner join ZoneList l 
      on z.parent_id = l.id 
    ) 
select id 
    , name + ' (' + parent_name + ')' as description 
from ZoneList 
where level = 4 
order by ZoneList.name 

Answer 2

为common table expression列清单是不t你会在哪里投出这个价值。

with ZoneList (id, name, parent_name, parent_id, level) as (
select 
    id 
    , name 
    , cast(('') as varchar(45)) as parent_name 
    , parent_id 
    , 1 as level 
from products.zones where id = 1 

union all 

select 
    z.id 
    , z.name 
    , cast((l.name) as varchar(45)) as parent_name 
    , z.parent_id 
    , level + 1 
from products.zones z 
    inner join ZoneList l 
    on z.parent_id = l.id 
) 
select 
    id 
    , name + ' (' + parent_name + ')' as description 
from ZoneList 
where level = 4 
order by ZoneList.name