sql - BigQuery 用户定义函数错误 - 无法在查询中调用 UDF

Question

我正在尝试使用我在 BQ 数据集中创建的用户定义函数。

这是UDF：

CREATE OR REPLACE FUNCTION `project.dataset.CountHolidays`(START_DATE DATETIME, 
SLA INT64, OPERATION STRING) AS (
(
        SELECT
            SUM(CASE WHEN OPERATION = 'OPTION 1' THEN OPTION_1
                WHEN OPERATION = 'OPTION 2' THEN OPTION_2
                WHEN OPERATION = 'OPTION 3' THEN OPTION_3
                WHEN OPERATION = 'OPTION 4' THEN OPTION_4
                WHEN OPERATION = 'OPTION 5' THEN OPTION_5
                WHEN OPERATION = 'OPTION 6' THEN OPTION_6
            END)
    FROM
        `project.dataset.calendar` cal
    WHERE
        cal.CALENDAR_DATE BETWEEN START_DATE AND DATE_ADD(START_DATE, INTERVAL SLA DAY)
)

);

我试图在这样的查询中调用 UDF：

SELECT 
ORDER_NUMBER,
 `project.dataset.CountHolidays`(DATE(PICK_RELEASE_DATE), EBS_SERVICE_DAYS, WAREHOUSE_ID) as CountHolidays
FROM `different_project.different_dataset.view_name`

我收到以下错误并且无法运行查询：

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

有人知道这里的错误是什么吗？几个小时以来一直在挠头。谢谢。

标签： sqlgoogle-bigqueryuser-defined-functions

Answer 1

您的选择引用了 UDF，它被“翻译”为不受支持的左连接 - 因此出现错误！

尝试以下版本的 UDF - 我认为应该可以

CREATE OR REPLACE FUNCTION `project.dataset.CountHolidays`(START_DATE DATE, 
SLA INT64, OPERATION STRING) AS (
(
        SELECT
            SUM(IF(cal.CALENDAR_DATE BETWEEN START_DATE AND DATE_ADD(START_DATE, INTERVAL SLA DAY),
            CASE WHEN OPERATION = 'OPTION 1' THEN OPTION_1
                WHEN OPERATION = 'OPTION 2' THEN OPTION_2
                WHEN OPERATION = 'OPTION 3' THEN OPTION_3
                WHEN OPERATION = 'OPTION 4' THEN OPTION_4
                WHEN OPERATION = 'OPTION 5' THEN OPTION_5
                WHEN OPERATION = 'OPTION 6' THEN OPTION_6
            END, 0))
    FROM
        `project.dataset.calendar` cal
));