title: How to nest CTE properly
body: <p>This question was asked few other times, but I still did not manage to sort out the right answer or proper way to do this:</p>
  
  <p>...</p>
  
  <pre><code>;WITH CTE AS
  (
    SELECT * FROM ...
  )
  SELECT *, [dbo].[udf_BetaInv](A, B, C, D) AS 'Loss'
  FROM CTE
  WHERE (Loss &gt;= @MinRetention)
  </code></pre>
  
  <p>This does not work and I cannot create the stored procedure, clearly I cannot use Loss in the WHERE because does not exist in that scope.</p>
  
  <p>I would like to use another CTE to wrap this one so I can put the WHERE on the outer one but not does not seem to work, tried this:</p>
  
  <pre><code>;WITH CTE AS
  (
    SELECT * FROM ...
  )
  SELECT *, [dbo].[udf_BetaInv(A, B, C, D) AS 'Loss'
  FROM CTE,
  RESULTS AS
  (SELECT * FROM CTE)
    SELECT *
    FROM RESULTS
    WHERE (Loss &gt;= @MinRetention)
  </code></pre>
  
  <p>But it does not compile in SQL Server, I get an error that a '(' is misplaces many rows above but has nothing to do, if I remove the second CTE it works fine.</p>
  
  <p>I only want to avoid code duplication, not want to call my [udf_BetaInv] twice in the select and also in the where.</p>