雪花SQL汇编错误与声明块创建任务创建

Question

尝试创建一个具有两个声明的VARCHAR变量的任务（从雪花工作表）时，给出了AN＆lt; eof＆gt;的错误。在声明第一个变量时在线上。我不确定为什么会发生这种情况，因为在雪花文档中的示例中，有关创建任务创建相同的语法以创建变量的示例。

这是查询。

create or replace task TEST_STORED_PROC_TASK
    warehouse = COMPUTE_XS
    schedule = '1 minute'
AS
DECLARE
    delete_query VARCHAR;
    insert_query VARCHAR;
BEGIN
    delete_query := 'DELETE FROM TEST_TASK_TBL_DEST
            USING TEST_STORED_PROC_TASK_STREAM
            WHERE TEST_TASK_TBL_DEST.DATA_INDEX = TEST_STORED_PROC_TASK_STREAM.DATA_INDEX
                  AND TEST_STORED_PROC_TASK_STREAM.METADATA$ISUPDATE;';
    insert_query := 'INSERT INTO TEST_TASK_TBL_DEST (DATA_INDEX ,COMP_ID ,ACCOUNT_ID ,COMP_VERSION ,NAME ,DESCRIPTION ,OBJECT_DICT ,ACTION_TYPE ,CONNECTOR_TYPE ,OUTPUT_PROFILE ,   PARAMETER_PROFILE)
              WITH DEDUPED_COMP AS (
                SELECT DATA_INDEX,COMP_ID,ACCOUNT_ID,COMP_VERSION,NAME, DESCRIPTION, PROPERTIES,  ROW_NUMBER() OVER (PARTITION BY COMP_ID ORDER BY COMP_VERSION DESC) AS ROW_NUM
                FROM TEST_STORED_PROC_TASK_STREAM
                WHERE METADATA$ACTION = \'INSERT\'
                QUALIFY 1 = ROW_NUM
              ),
              GROUPED_PROPS AS (
                SELECT ANY_VALUE(DATA_INDEX) AS DATA_INDEX, COMP_ID, ANY_VALUE(ACCOUNT_ID) AS ACCOUNT_ID, ANY_VALUE(COMP_VERSION) AS COMP_VERSION,
                       ANY_VALUE(NAME) AS NAME, ANY_VALUE(DESCRIPTION) AS DESCRIPTION, OBJECT_AGG(DISTINCT XMLGET(PROPS.Value,\'Name\'):"$"::string,
                       XMLGET(PROPS.Value,\'Value\'):"$"::VARIANT) AS OBJECT_DICT
                FROM DEDUPED_COMP,
                LATERAL FLATTEN(INPUT=>PROPERTIES, MODE=> \'ARRAY\', OUTER=>TRUE) PROPS
                GROUP BY COMP_ID
              )
              SELECT *, OBJECT_DICT:"action-type"::string AS ACTION_TYPE, OBJECT_DICT:"connector-type"::string AS CONNECTOR_TYPE,
                     OBJECT_DICT:"output-profile"::string AS OUTPUT_PROFILE, OBJECT_DICT:"parameter-profile"::string AS PARAMETER_PROFILE
              FROM GROUPED_PROPS;
            ';
    call RUN_TASK_QUERIES(ARRAY_CONSTRUCT(:delete_query, :insert_query));
END;

这是错误

SQL compilation error: syntax error line 6 at position 25 unexpected '<EOF>'.

Answer 1

使用Snowsight时，该代码是无错误执行的。

如果使用classicui，则使用立即执行$$ ... $$将其编译：

create or replace task TEST_STORED_PROC_TASK
    warehouse = COMPUTE_XS
    schedule = '1 minute'
AS
EXECUTE IMMEDIATE $
DECLARE
    delete_query VARCHAR;
    insert_query VARCHAR;
BEGIN
    delete_query := 'DELETE FROM TEST_TASK_TBL_DEST
            USING TEST_STORED_PROC_TASK_STREAM
            WHERE TEST_TASK_TBL_DEST.DATA_INDEX = TEST_STORED_PROC_TASK_STREAM.DATA_INDEX
                  AND TEST_STORED_PROC_TASK_STREAM.METADATA$ISUPDATE;';
    insert_query := 'INSERT INTO TEST_TASK_TBL_DEST (DATA_INDEX ,COMP_ID ,ACCOUNT_ID ,COMP_VERSION ,NAME ,DESCRIPTION ,OBJECT_DICT ,ACTION_TYPE ,CONNECTOR_TYPE ,OUTPUT_PROFILE ,   PARAMETER_PROFILE)
              WITH DEDUPED_COMP AS (
                SELECT DATA_INDEX,COMP_ID,ACCOUNT_ID,COMP_VERSION,NAME, DESCRIPTION, PROPERTIES,  ROW_NUMBER() OVER (PARTITION BY COMP_ID ORDER BY COMP_VERSION DESC) AS ROW_NUM
                FROM TEST_STORED_PROC_TASK_STREAM
                WHERE METADATA$ACTION = \'INSERT\'
                QUALIFY 1 = ROW_NUM
              ),
              GROUPED_PROPS AS (
                SELECT ANY_VALUE(DATA_INDEX) AS DATA_INDEX, COMP_ID, ANY_VALUE(ACCOUNT_ID) AS ACCOUNT_ID, ANY_VALUE(COMP_VERSION) AS COMP_VERSION,
                       ANY_VALUE(NAME) AS NAME, ANY_VALUE(DESCRIPTION) AS DESCRIPTION, OBJECT_AGG(DISTINCT XMLGET(PROPS.Value,\'Name\'):"$"::string,
                       XMLGET(PROPS.Value,\'Value\'):"$"::VARIANT) AS OBJECT_DICT
                FROM DEDUPED_COMP,
                LATERAL FLATTEN(INPUT=>PROPERTIES, MODE=> \'ARRAY\', OUTER=>TRUE) PROPS
                GROUP BY COMP_ID
              )
              SELECT *, OBJECT_DICT:"action-type"::string AS ACTION_TYPE, OBJECT_DICT:"connector-type"::string AS CONNECTOR_TYPE,
                     OBJECT_DICT:"output-profile"::string AS OUTPUT_PROFILE, OBJECT_DICT:"parameter-profile"::string AS PARAMETER_PROFILE
              FROM GROUPED_PROPS;
            ';
    call RUN_TASK_QUERIES(ARRAY_CONSTRUCT(:delete_query, :insert_query));
END;
$;