How to nest CTE properly

Question

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:

...

;WITH CTE AS
(
  SELECT * FROM ...
)
SELECT *, [dbo].[udf_BetaInv](A, B, C, D) AS 'Loss'
FROM CTE
WHERE (Loss >= @MinRetention)

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.

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:

;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 >= @MinRetention)

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.

I only want to avoid code duplication, not want to call my [udf_BetaInv] twice in the select and also in the where.

Answer 1

You have an intermediate SELECT that you should not have. This should work:

;WITH CTE AS
(
  SELECT * FROM ...
),
RESULTS AS
(
  SELECT *, [dbo].[udf_BetaInv(A, B, C, D) AS 'Loss'
  FROM CTE
)
SELECT *
FROM RESULTS
WHERE (Loss >= @MinRetention)