SQL recursive CTE query error 'invalid column name - level'

Question

Trying to write a recursive CTE query, keep getting the following error: level, invalid column name

This is the query, where am I going wrong?

WITH OwnerHierarchy AS (
   SELECT PairID, 
          ChildID, 
          ParentID, 
          0 AS level
     FROM BusinessHierarchy
   UNION ALL
   SELECT e.PairID, 
          e.ChildID, 
          e.ParentID, 
          level + 1 AS level
     FROM BusinessHierarchy AS e 
     JOIN BusinessHierarchy AS eh ON e.ParentID = eh.ParentID)
 SELECT PairID, 
        ChildID, 
        ParentID, 
        level
    FROM OwnerHierarchy AS OwnerHierarchy_1
 ORDER BY level, ChildID, ParentID

This is ms sql server 2005.

Answer 1

You need OwnerHierarchy somewhere in the second half of the CTE to be recursive. Instead, you have BusinessHierarchy twice.

Answer 2

Just guessing at the exact requirements, but something like this.

WITH OwnerHierarchy(PairID, ChildID, ParentID, level)
AS 
(
     SELECT PairID, ChildID, ParentID, 0 AS level
     FROM BusinessHierarchy
     WHERE ParentID IS NULL
     UNION ALL
     SELECT e.PairID, e.ChildID, e.ParentID, level + 1 AS level
     FROM BusinessHierarchy AS e 
     INNER JOIN OwnerHierarchy AS eh 
     ON e.ParentID = eh. ChildID
     )
 SELECT     PairID, ChildID, ParentID, level
 FROM         OwnerHierarchy AS OwnerHierarchy_1
 ORDER BY level, ChildID, ParentID

Answer 3

There's three issues with what you posted:

WITH OwnerHierarchy AS (
   SELECT a.pairid, 
          a.childid, 
          a.parentid, 
          0 AS level
     FROM BusinessHierarchy a
    WHERE a.parentid IS NULL -- Point #1, see below
   UNION ALL
   SELECT b.PairID, 
          b.ChildID, 
          b.ParentID, 
          oh.level + 1 AS level
     FROM BusinessHierarchy AS b
     JOIN OwnerHierarchy oh ON oh.childid = b.parentid) -- Points #2 & 3, see below
 SELECT x.PairID, 
        x.ChildID, 
        x.ParentID, 
        x.level
    FROM OwnerHierarchy x
 ORDER BY x.level, x.ChildID, x.ParentID

Points:

1. The top half of the UNION is missing what determines what the root nodes/records/rows are. I've assumed, hopefully correctly...
2. You were joining BusinessHierarchy to itself, when the reference should be the CTE name
3. The relationship between the CTE and original table needs to be correct, and in the correct direction

Props for using table aliases, but they weren't consistently used, and they should be as brief as possible. You don't need to use the AS when defining them, just a matter of style on that part.

Conclusion

---

I'm thinking the table aliases will deal with the level column not being found issue, but not 100%.