title: Using a column in a CTE that does not exist in another table
body: <p>I am new to writing recursive queries and I am trying to write my first one. I am trying to determine levels of a corporate hierarchy and have the result table include two columns- the consultant ID and the level that has been determined by the recursive query. In the CTE, I can't figure out how to reference &quot;level&quot; since it is a column that doesn't yet exist in a table. What am I doing wrong?</p>
  <pre><code>WITH consultantsandlevels
      (c.&quot;ConsultantDisplayID&quot;,
      consultantsandlevels.&quot;level&quot;
      )
  
  AS
  (
  
  SELECT c.&quot;ConsultantDisplayID&quot;,
      0
  
  FROM flight_export_consultant AS c
  
  WHERE c.&quot;ParentPersonDisplayID&quot; IS NULL
  
  UNION all
  
  SELECT c.&quot;ConsultantDisplayID&quot;,
  
      c.&quot;ParentPersonDisplayID&quot;,
  
      consultantsandlevels.&quot;level&quot; + 1
  FROM flight_export_consultant
  
  JOIN consultantsandlevels ON c.&quot;ParentPersonDisplayID&quot; = consultantsandlevels.&quot;ConsultantDisplayID&quot;
  )
  
  SELECT *
  
  FROM consultantsandlevels;
  </code></pre>