I am having trouble understanding why a CTE can't be referenced from a WHERE clause.
In the following code, the first query works fine, but in the second query, simply replacing the value in the WHERE clause with a CTE which calculates that value, produces the error "Unknown column 'cte' in 'where clause'".
# This query works
SELECT * FROM stock_prices
WHERE Country_Exchange_Code = 'T' AND Stock_Code IN ('1332');
# But this query produces an error
WITH
cte AS (SELECT '1332')
SELECT * FROM stock_prices
WHERE Country_Exchange_Code = 'T' AND Stock_Code IN (cte);
# Here is some data to test these two queries with
CREATE TABLE stock_prices (Country_Exchange_Code VARCHAR(2), Stock_Code VARCHAR(4), Date DATE, Close FLOAT);
INSERT INTO stock_prices VALUES
("T", "1301", '2019-10-29', 75.2),
("T", "1301", '2019-10-30', 76.6),
("T", "1301", '2019-10-31', 77.6),
("T", "1301", '2019-11-01', 77.2),
("T", "1332", '2019-10-29', 52.5),
("T", "1332", '2019-10-30', 49.7),
("T", "1332", '2019-10-31', 50.8),
("T", "1332", '2019-11-01', 50.4),
("T", "1333", '2019-10-29', 13.9),
("T", "1333", '2019-10-30', 13.8),
("T", "1333", '2019-10-31', 14.3),
("T", "1333", '2019-11-01', 14.4);
You can do:
WITH
cte AS (SELECT '1332' as code)
SELECT * FROM stock_prices
WHERE Country_Exchange_Code = 'T' AND Stock_Code IN (select code from cte);
Result:
Country_Exchange_Code Stock_Code Date Close
---------------------- ----------- ----------- -----
T 1332 2019-10-29 52.5
T 1332 2019-10-30 49.7
T 1332 2019-10-31 50.8
T 1332 2019-11-01 50.4
Your query had two issues:
IN requires a formal subquery with the form SELECT ...as code.See running example at DB Fiddle.
It sounds like you are thinking of a cte as some kind of variable instead of thinking of it as a virtual table.
You can't do IN (cte) just like you can't do IN (sometablename); it needs to be IN (select somecol from sometablename).