How to select values with a column alias?

Question

If I do this in SQLite3:

SELECT *
FROM (VALUES (1), (2), (3)) AS "tbl.col"
WHERE "tbl"."col" = 1

I get: no such column: tbl.col

What is the correct way?

Thanks!

Answer 1

I think you are looking for this:

SELECT *
FROM (VALUES (1), (2), (3)) AS tbl(col)
WHERE tbl.col = 1;

Note that when you escape an identifier (using "tbl.col"), then that is one name that has a period in it. Not two names.

EDIT:

I would have expected the above to work, but it doesn't in SQLite. One alternative is to use a CTE:

with tbl(col) as (
      VALUES (1), (2), (3)
     )
SELECT *
FROM tbl
where tbl.col = 1