How could I call a 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
)
Not only can you define multiple CTEs and reference them in a single SELECT statement, but you can also have a CTE that references another CTE. In order to do this all you need to do is define the referenced CTE prior to using it. Here is an example where my first CTE is referenced inside the second CTE definition.
Specifying more than one WITH clause in a CTE isn't allowed. For example, if a CTE query definition contains a subquery, that subquery can't contain a nested WITH clause that defines another CTE.
After you've defined the first CTE, it is separated from the second one only by the comma, i.e. you write WITH only once. After that, it doesn't matter how many CTEs you define; it's only important that you separate them by comma and start every CTE using its name.
The first CTE is separated from the second one by a comma. This also goes if you write more than two CTEs: all the CTEs are separated by a comma. However, no matter how many CTEs you have, there's no comma between the last CTE and the main query.
You have to join both like with a normal 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 have appended SELECT * FROM cte2
since CTE's cannot "stand" alone.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With