Table Aliases into Subqueries

(Submitting here to assist other Snowflake Users who may run into similar challenges… Interested to see if there are any additional recommendations beyond what’s bee provided already.)

Why doesn’t table alias work into subqueries?

I was using a sample table select query but it doesn’t work when I coded a table alias.

select * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.STORE as t
where 
t.S_REC_START_DATE = (
          select max(i.S_REC_START_DATE) from t as i
              where i.S_REC_START_DATE < '2000-01-01'
        )

I got a SQL compilation error: Object 'T' does not exist.

is not possible to use table alias?

Answer

(Previously provided by Mike Walton, a tenured member of Snowflake’s Professional Services team)

You can, but not that way. You should use a CTE, instead:

WITH t as (
  select * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.STORE
)
select * FROM t
where t.S_REC_START_DATE = (
         select max(S_REC_START_DATE) as S_REC_START_DATE from t
              where S_REC_START_DATE < '2000-01-01'
        )

Any other ideas and/or recommendations?