Big Query - User Defined Function - Scalar Subquery Error

Question

I am trying to re-create a user defined function based on the query below, however, when I ran it, it returned me this error instead. Appreciate if anyone here knows a workaround.

Scalar subquery produced more than one element:

create or replace function `dataset.list_of_days`
(user_id string, start_date date, end_date date) AS 
((
with temp as (
  select user_id, day from
  unnest(generate_date_array(start_date, end_date)) day)   

select as struct row_number() over (partition by user_id order by day asc) as row_num,
       user_id, day
from temp
  ));

with temp as (
select '100110' as user_id, date('2020-01-31') as start_date,
       date('2020-02-28') as end_date )
       
select dataset.list_of_days(user_id, start_date, end_date)
from temp;

Correct me if I am wrong, but I may have mis-understood what is the purpose of a user defined function — Rootie

Yun Zhang Yun Zhang · Accepted Answer · 2020-11-25T08:49:25

BigQuery's UDF could only return a scalar value, it seems you don't have too huge of an output from inside the UDF, you may consider to rewrite it as

create or replace function `dataset.list_of_days`
(user_id string, start_date date, end_date date) AS 

(ARRAY(   -- NOTE: ARRAY() was added to you original query

with temp as (
  select user_id, day from
  unnest(generate_date_array(start_date, end_date)) day)   

select as struct row_number() over (partition by user_id order by day asc) as row_num,
       user_id, day
from temp
  ));

with temp as (
select '100110' as user_id, date('2020-01-31') as start_date,
       date('2020-02-28') as end_date )
       
select dataset.list_of_days(user_id, start_date, end_date)
from temp;

Note now the return type of the UDF is ARRAY< STRUCT<INT64, STRING, DATE> >, you may unnest the array if you'll need it as a table with multiple rows.

Big Query - User Defined Function - Scalar Subquery Error

1 Answers