How to properly embed CTE

Question

This question has been asked several times, but I still could not figure out the correct answer or the correct 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 a stored procedure, I cannot use Loss in WHERE, because it does not exist in this area.

I would like to use another CTE to wrap this so that I can put WHERE on the external, but it doesn't 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 '(' offsets many lines above, but has nothing to do if I delete the second CTE, it works fine.

I just want to avoid code duplication, I don't want to call my [udf_BetaInv] in select twice, and also in that place.

Answer 1

You have an intermediate SELECT that you shouldn't 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) 

Answer 2

Obviously, the problem with the first query is that "Loss" is just a column alias and cannot be used in the WHERE . You are correct that using this in a CTE will avoid duplication of expression. Here's how you do it:

 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 the side of the note. See if you can break the habit of running CTE definitions with ;WITH and use the habit of ending all of your SQL statements with a colon instead. This is a more readable and best practice.

Answer 3

The following is an example of nested CTE .

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