Call one CTE in another CTE

Question

How can I call CTE in another CTE?

WITH cte1 AS ( SELECT City.* FROM City WHERE (City.CityName COLLATE SQL_Latin1_General_CP1_CI_AI) LIKE 'são paulo' ) , cte2 AS ( SELECT Imovel.Imovel_Id FROM Imovel WHERE Imovel.Number = 311 AND Imovel.ZIPCode = '30280490' AND Imovel.Complement = '' AND Imovel.Street = 'Do furquim' -- the line below has an error in cte.City_Id AND Imovel.City_Id = cte1.City_Id ) 

Answer 1

You need to join like a regular table:

 WITH cte1 AS (SELECT city.* FROM city WHERE ( city.cityname COLLATE sql_latin1_general_cp1_ci_ai ) LIKE 'são paulo'), cte2 AS (SELECT imovel.imovel_id FROM imovel INNER JOIN cte1 ON imovel.city_id = cte1.city_id WHERE imovel.number = 311 AND imovel.zipcode = '30280490' AND imovel.complement = '' AND imovel.street = 'Do furquim') SELECT * FROM cte2 

Note that I added SELECT * FROM cte2 , since the CTE cannot "stand" alone.