Big query error - Scalar subquery produced more than one result

Question

select
  CASE LOWER(channel)
    WHEN 'sem' THEN 
      CASE
        WHEN (LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)]) = 'us') THEN 'us independents'
        WHEN (LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)]) = 'intl') THEN 'intl independents'
        WHEN (LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)]) = 'part') THEN 'partners'
        WHEN (LOWER(SPLIT(campaign_name,'_')[SAFE_OFFSET(9)]) = 'invest') THEN 'domain investors & registrars'
        WHEN campaign_name IS NOT NULL  or channel IS NOT NULL THEN 
          (select  (business_unit)
           from dgapbigquery.bi_reports.channel_campaign_exception_lookup)
        ELSE 'unknown'
      END
    END
from 
  gdgapbigquery.bi_reports.channel_campaign_exception_lookup

Answer 1

try

array(
  select  business_unit
  from gdgapbigquery.bi_reports.channel_campaign_exception_lookup
)