[SOLVED] Write a query on top of the results of LIST clause

Write a query on top of the results of LIST clause

The command LIST returns a table-like object (Snowflake docs). Thus I'm wondering whether it is possible to write a query on top of this output:

I've tried something like:

SELECT t.name FROM (LIST @dbname.schname.stgname) as t;

Which results in the following error:

Syntax error: unexpected '@dbname.schname.stgname'. (line 1)

At the bottom line, I would like to use such a strategy to get the filenames of files which are still pending for processing (assuming I will keep records of all processed files in another table). Then, I was planning to run something like this:

SELECT t.name
FROM
    (LIST @dbname.schname.stgname) as t
    LEFT JOIN dbname.schname.tb_processed_files p on p.md5 = t.md5
WHERE p.md5 is null;

Solution

you can execute a query and then use the Snowflake's result_scan function to get the result.

LIST @my_int_stage;  
select * from table(result_scan(last_query_id()));