BigQuery. Scalar subquery produced more than one element

1
votes

Here is a query which results in this error: Query Failed Error: Scalar subquery produced more than one element

select date
       ,isdepositor
       ,category 
       ,(select distinct dd from unnest(d.subcategory) dd) subcategory
       ,dau
from(
       select date
       ,isdepositor
       ,'Level' as Category
       ,array(select 'Daily' union all select 'Weekly' union all select 'Monthly') subcategory
       , dau 
       from DWH.vT_DAU
) d

DWH.vT_DAU is a view, where DAU is calculated for each date and boolean field 'isdepositor'.

I need to create custom fields 'Category' and 'Subcategory' where the same for each 'date' and 'isdepositor' DAU will be displayed.

I found some similar question regarding this bigquery error here, however, any solution didn't work for me.

Any help would be appreciated. Thank you!

1
sqlgoogle-bigquery
Managed to solve this using UNNEST in JOIN. However, would appreciate if anyone has ideas on how to UNNEST in SELECT - Porada Kev

1 Answers

4
votes

I need to create custom fields 'Category' and 'Subcategory' where the same for each 'date' and 'isdepositor' DAU will be displayed.

Below does exactly this 

#standardSQL
SELECT 
  date
  ,isdepositor
  ,'Level' AS Category
  ,subcategory
  , dau 
FROM `DWH.vT_DAU`
CROSS JOIN 
  (SELECT 'Daily' subcategory UNION ALL SELECT 'Weekly' UNION ALL SELECT 'Monthly')

above is equivalent to below - which is most likely what you ended up (based on your comment) 

#standardSQL
SELECT 
  date
  ,isdepositor
  ,'Level' AS Category
  ,subcategory
  , dau 
FROM `DWH.vT_DAU`
, UNNEST(['Daily', 'Weekly', 'Monthly']) subcategory