Can I use a table from the WITH clause in the IN clause?

Question

In attempt to write more readable SQL code by following this recommendation, I'm trying to use the WITH clause.

It works here:

WITH
    t AS
    (
        SELECT
            *
        FROM
            TABLE1
        WHERE
            COL1 = 'foo'
    )
SELECT
    *
FROM
    t
WHERE
    COL2 > 42

But it doesn't work in the following case:

WITH
    t AS
    (
        SELECT
            COL1
        FROM
            TABLE1
    )
SELECT
    *
FROM
    TABLE2
WHERE
    COL2 IN t

It returns the following error:

1) [Code: -206, SQL State: 42703] "T" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.22.29

Just in case it's not clear what I mean by the non-working query above, here is what:

SELECT
    *
FROM
    TABLE2
WHERE
    COL2 IN
    (
        SELECT
            COL1
        FROM
            TABLE1
    )

How can I use a table for the WITH clause in the IN clause?

Answer 1

"T" acts as a table so you have to select a column in order to compare it with "IN"

WITH
t AS
(
    SELECT
        COL1
    FROM
        TABLE1
)
SELECT
 *
FROM
TABLE2
WHERE
COL2 IN ( SELECT COL FROM t )