Snowflake script through ODBC not returning data

I created a SQL script to dynamically change a table's column names to its column comments.

Here is my code:

sql1 <- sql(
  "
  DECLARE
      res RESULTSET;
      query_string VARCHAR;
      tbl VARCHAR;
      tbl_sch VARCHAR;
  BEGIN
      tbl := 'V02_SAP_TVOS';
      tbl_sch := 'DRTRV02';
      query_string := CONCAT(
        'SELECT ',
        (
        SELECT
          LISTAGG(COLUMN_NAME || ' AS \"' || COMMENT || '\"', ', ')
                  WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS dynamic_query
          FROM
              INFORMATION_SCHEMA.COLUMNS
          WHERE
                TABLE_NAME = :tbl
            AND TABLE_SCHEMA = :tbl_sch
            ),
            ' FROM ', :tbl_sch, '.', :tbl
        );
  
        res := (EXECUTE IMMEDIATE :query_string);
        RETURN TABLE(res);
  END;
"
)

myconn <- 
  DBI::dbConnect(
    odbc::odbc(),
    Driver = "SnowflakeDSIIDriver",
    Server = "xxxxx.snowflakecomputing.com",
    Authenticator = "externalbrowser",
    uid = "yyyyy",
    database = "dev_main"
  )

DBI::dbGetQuery(myconn, sql1)

I can run this query on app.snowflake.com no problem but in R, this result in an empty dataframe (0x0). I can query normal SELECT statements no problem in R.

What's going on here? Is there something I need to do to the Snowflake ODBC driver parameter to be able to use scripts?

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.