How to use cte?

Question

I have a simple construction with cte like:

;WITH Base_cte AS 
    (
        SELECT
        FROM
        WHERE
    )

SELECT
FROM
   (
       SELECT
       FROM Base_cte
       WHERE
    )

SELECT
FROM Base_cte
WHERE

which returns for the second select with cte:

Invalid object name 'Base_cte'.

I have no idea why it can not recognize the cte for the second time. The code is too large to paste here, but fair simple basically I think I must missing something fundamental. Any hints would be appreciated.

Answer 1

Do you need the 2nd CTE? Why not:

;with base_cte as
(
    select
    from
    where
)

select *
from base_cte
join
where

Answer 2

You need to use this format

;WITH Base_cte AS 
    (
        SELECT
        FROM
        WHERE
    )
,CTE1 AS
(
       SELECT
       FROM Base_cte
       WHERE
)
SELECT * FROM CTE1