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
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)
.