Hierarchical SQL query not returning level

Question

I have a typical SQL Server hierarchical query:

WITH bhp AS (
   SELECT name, 0 AS level
   FROM dbo.BhpNode
   WHERE parent_id IS NULL
   UNION ALL
   SELECT a.name, level + 1
   FROM dbo.BhpNode a
   INNER JOIN dbo.BhpNode b
      ON b.bhp_node_id = a.parent_id )
  SELECT * FROM bhp

This seems to match the various examples of hierarchical queries I've found around the web, but for some reason it's producting this error:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'level'.

I'm sure I'm missing something obvious, but I've stared at it too long to see it. Any idea where I'm going wrong?

Answer 1

In the recursive section of the CTE, one of the tables you reference should be the CTE itself, shouldn't it? At the moment you are just self-joining BhpNode, and it doesn't have a level column itself.

Answer 2

Your query isn't recursive - you have to select from bhp inside the second part of the recursive CTE. Try this instead:

WITH bhp AS (
   SELECT *, 0 AS [level]
   FROM dbo.BhpNode
   WHERE parent_id IS NULL
   UNION ALL
   SELECT b.*, [level] + 1
   FROM bhp a
   INNER JOIN dbo.BhpNode b
   ON a.bhp_node_id = b.parent_id)
SELECT * FROM bhp