Error Syntax with 'WITH' 1

[ReportingAnalyst]

What is the syntax error near the WITH statement?

Thanks.

with organization (depth, emp, boss, chain)
as
( select 1 as depth,
         emp,
         boss,
         cast(rtrim(emp) as varchar (400))
    from tree
  where boss is null
  union all
  select a.depth + 1 as depth,
         b.emp,
         b.boss,
         cast (a.chain + ', ' + rtrim(b.emp) as varchar(400))
    from organization a
   inner join tree b
      on a.emp = b.boss
)

Error I get is:

Incorrect syntax near the keyword 'with'.

 

[SQLDenis]

this should work, keep in mind that this will work on SQl server 2005 only and not on 2000

with organization (depth, emp, boss, chain)
as
( select 1 as depth,
         emp,
         boss,
         cast(rtrim(emp) as varchar (400))
    from tree
  where boss is null
  union all
  select a.depth + 1 as depth,
         b.emp,
         b.boss,
         cast (a.chain + ', ' + rtrim(b.emp) as varchar(400))
    from organization a
   inner join tree b
      on a.emp = b.boss
)

select * from organization

2000 syntax is like this

select * from 
( select 1 as depth,
         emp,
         boss,
         cast(rtrim(emp) as varchar (400))
    from tree
  where boss is null
  union all
  select a.depth + 1 as depth,
         b.emp,
         b.boss,
         cast (a.chain + ', ' + rtrim(b.emp) as varchar(400))
    from organization a
   inner join tree b
      on a.emp = b.boss
) organization

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ReportingAnalyst]

Thanks. Changes from one version to another can be painful in the syntax changes as well. I work SQL Server 2000.