CODEWITHSUNDEEP
sqlgoogle-bigquery
Porada Kev
Porada Kev

Reputation: 513

BigQuery. Scalar subquery produced more than one element

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!

Upvotes: 1

Views: 3299

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

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

Upvotes: 4

Related Questions