BigQuery User Defined Function Error - Unable to Call UDF In Query

Question

I am trying to use a user defined function that I have created in my BQ dataset.

Here is the 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)
)

);

I am trying to call the UDF within a query like this:

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

I get the following error and am unable to run the query:

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

Does anybody know what the error is here? Been scratching my head for a few hours now. Thanks.

Mikhail Berlyant · Accepted Answer · 2021-07-23 00:13:40Z

1

Your select references UDF which gets "translated" into left join that is not supported - thus the error!

Try below version of your UDF - should work I think

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

answered Jul 23, 2021 at 0:13

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

z.rubi Over a year ago

This worked. Thank you! So the issue was the where clause was making the UDF act as a left join? Would not have expected that

Mikhail Berlyant Over a year ago

yes. left join w/o equality is not supported in BigQuery, so it is quite usual issue. and it is always about re-writing stuff in a way that avoids such left join. So, glad my version works for you :o)

Collectives™ on Stack Overflow

BigQuery User Defined Function Error - Unable to Call UDF In Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related