Script works on Snowsight but error on DataGrip

Answered

I'm using DataGrip 2023.3.3.

I created following script to rename column names with their comments because many tables I deal with have too many columns with random column names. I'm basically creating a SELECT statement and executing it.

DECLARE
    res RESULTSET;
    query_string VARCHAR;
    tbl VARCHAR;
    tbl_sch VARCHAR;
BEGIN
    tbl := 'table1';
    tbl_sch := 'schema1';
    query_string := CONCAT(
      'SELECT ',
      (
      SELECT
        LISTAGG(COLUMN_NAME || ' AS "' || COALESCE(COMMENT, COLUMN_NAME) || '"', ', ')
                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;

 

This script works on Snowsight (app.snowflake.com) but this doesn't work on DataGrip. Any help would be appreciated!

0
4 comments

What is the error thrown? Is it on the code highlighting or when you run the query? Any screenshots are helpful

0

Please fill out a new bug report directly on https://youtrack.jetbrains.com/newIssue?project=DBE so we can involve our developers. In case this runs on Snowflake this might be a bug

0

Ok, I accidentally found out how to run this in DataGrip. If I select all codes, this does run. However, if I run “the whole script” execute, it errors out.

0

Please sign in to leave a comment.