Query table selected with another query in Snowflake

Question

I have a table that changes name every day and I want to write a query that will "find" it and work on it without the need to change the name of the table in my query every day.

I can easily find it using a query like that

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_TABLE_CONST_NAME' ORDER BY CREATED DESC LIMIT 1

This will return the table_name I want to query, eg: randomstring_TABLE_CONST_NAME

I tried using it like that but it didn't work (obviously) because it queries the result, not the table

SELECT * FROM (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_TABLE_CONST_NAME' ORDER BY CREATED DESC LIMIT 1) table

And I would like it to evaluate the query result and query it as ``SELECT * FROM randomstring_TABLE_CONST_NAME table`

Any ideas? Thank you

Answer 1

A possible solution is writing a stored procedure that will find your table and query it.

CREATE OR REPLACE PROCEDURE get_data_from_latest_table()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    var tableName = '';
    var resultSet;
    var finalQuery = '';
    var output = '';

    var query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%_TABLE_CONST_NAME' ORDER BY CREATED DESC LIMIT 1;"; //your query to get the table name
    resultSet = snowflake.execute({sqlText: query});


    if (resultSet.next()) {
        tableName = resultSet.getColumnValue(1);
    }


    if (tableName !== '') {
        finalQuery = "SELECT * FROM " + tableName; //your query from the table 
        resultSet = snowflake.execute({sqlText: finalQuery});

        while(resultSet.next()) {
            output += resultSet.getColumnValue(1) + ", "; 
        }
    }
    return output;
$$;

--call the procedure
CALL get_data_from_latest_table();

Answer 2

you can try this:

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  table_name VARCHAR DEFAULT (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%%_TABLE_CONST_NAME' ORDER BY CREATED DESC LIMIT 1);
  query VARCHAR ;
BEGIN
  query := 'SELECT * FROM '||table_name;
  rs := (EXECUTE IMMEDIATE :query );
  RETURN TABLE(rs);
END;
$$
;