How to nest CTE properly

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 开发者_JAVA技巧to avoid code duplication, not want to call my [udf_BetaInv] twice in the select and also in the where.

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)

Obviously the problem with the first query is that 'Loss' is just a column alias and can't be used in a WHERE clause. You're right that using it in a CTE would avoid duplicating the expression. Here's how you'd do that;

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

On a side note: See if you can break the habit of starting your CTE definitions with ;WITH and instead get into the habit of ending all your SQL statements with a semi-colon. It's more readable and better practice.

Below is the example of nested CTE.

with cte_data as 
(
    Select * from [HumanResources].[Department]
),cte_data1 as
(
    Select * from cte_data
)

select * from cte_data1