英文:
I am trying to create a UDTF in Snowflake but I get an error on RETURNS TABLE even though it matches the syntax
问题
以下是您提供的代码的中文翻译：
创建或替换函数 DQ_IS_NULL ("SCHEMA_NAME" VARCHAR(500), "TABLE_NAME" VARCHAR(500), "COLUMN_NAME" VARCHAR(500))
返回表 (TABLE_NME VARCHAR(500), COLUMN_NME VARCHAR(500), ISSUE_CODE VARCHAR(100), PARAMETERS VARCHAR(500), VALUE VARCHAR (500), ROW_HASH NUMBER(19,0), EXECUTION_TS TIMESTAMP)
语言 SQL
作为
'
选择
表名,
列名,
CASE
当 V 不为空时
THEN 'NULLed'
END AS ISSUE_CODE,
CASE
当列名不为空时
THEN '不允许为空'
END AS PARAMETERS,
HASH(*) AS ROW_HASH,
当前时间() AS EXECUTION_TS
从 连接(SCHEMA_NAME ,'.',TABLE_NAME)
其中列名为NULL
这个函数的目的是返回给定表中所有为空的行。
英文:
CREATE OR REPLACE FUNCTION DQ_IS_NULL ("SCHEMA_NAME" VARCHAR(500), "TABLE_NAME" VARCHAR(500), "COLUMN_NAME" VARCHAR(500))
RETURNS TABLE (TABLE_NME VARCHAR(500), COLUMN_NME VARCHAR(500), ISSUE_CODE VARCHAR(100), PARAMETERS VARCHAR(500), VALUE VARCHAR (500), ROW_HASH NUMBER(19,0), EXECUTION_TS TIMESTAMP)
LANGUAGE SQL
AS
'
SELECT
TABLE_NAME,
COLUMN_NAME,
CASE
WHEN V IS NOT NULL
THEN ''NULLed''
END AS ISSUE_CODE,
CASE
WHEN COLUMN_NAME IS NOT NULL
THEN ''NULLS not permitted''
END AS PARAMETERS,
HASH(*) AS ROW_HASH,
CURRENT_TIME() AS EXECUTION_TS
FROM CONCAT(SCHEMA_NAME ,''.'',TABLE_NAME)
WHERE COLUMN_NAME IS NULL
The function is intended to return all rows in a given table that are null
答案1
得分: 1
问题在于您使用'来表示函数定义的开始，但在函数体内也有一个'。您可以使用$$来解决此问题：
CREATE OR REPLACE TEMP FUNCTION DQ_IS_NULL ("SCHEMA_NAME" VARCHAR(500), "TABLE_NAME" VARCHAR(500), "COLUMN_NAME" VARCHAR(500))
RETURNS TABLE (TABLE_NME VARCHAR(500), COLUMN_NME VARCHAR(500), ISSUE_CODE VARCHAR(100), PARAMETERS VARCHAR(500), VALUE VARCHAR (500), ROW_HASH NUMBER(19,0), EXECUTION_TS TIMESTAMP)
LANGUAGE SQL
AS
$$
SELECT
TABLE_NAME,
COLUMN_NAME,
CASE
WHEN V IS NOT NULL
THEN 'NULLed'
END AS ISSUE_CODE,
CASE
WHEN COLUMN_NAME IS NOT NULL
THEN 'NULLS not permitted'
END AS PARAMETERS,
HASH(*) AS ROW_HASH,
CURRENT_TIME() AS EXECUTION_TS
FROM CONCAT(SCHEMA_NAME ,'',TABLE_NAME)
WHERE COLUMN_NAME IS NULL
$$;
请注意，然而还有另一个问题；在UDTF体内，您需要使用真实的表名，而不是CONCAT(SCHEMA_NAME ,''.'',TABLE_NAME)。
英文:
The problem is that you're using ' to denote the start of the function definition, but there is a ' within the body as well. You can use $$ to work around the issue:
CREATE OR REPLACE TEMP FUNCTION DQ_IS_NULL ("SCHEMA_NAME" VARCHAR(500), "TABLE_NAME" VARCHAR(500), "COLUMN_NAME" VARCHAR(500))
RETURNS TABLE (TABLE_NME VARCHAR(500), COLUMN_NME VARCHAR(500), ISSUE_CODE VARCHAR(100), PARAMETERS VARCHAR(500), VALUE VARCHAR (500), ROW_HASH NUMBER(19,0), EXECUTION_TS TIMESTAMP)
LANGUAGE SQL
AS
$$
SELECT
TABLE_NAME,
COLUMN_NAME,
CASE
WHEN V IS NOT NULL
THEN 'NULLed'
END AS ISSUE_CODE,
CASE
WHEN COLUMN_NAME IS NOT NULL
THEN 'NULLS not permitted'
END AS PARAMETERS,
HASH(*) AS ROW_HASH,
CURRENT_TIME() AS EXECUTION_TS
FROM CONCAT(SCHEMA_NAME ,''.'',TABLE_NAME)
WHERE COLUMN_NAME IS NULL
$$;
Note that there's another issue still, however; you need to use a real table name in the UDTF body, as opposed to CONCAT(SCHEMA_NAME ,''.'',TABLE_NAME).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论