Query "is valid" but produces an error: Scalar subquery produced more than one element

Question

This query “is valid” according to the BigQuery SQL editor. However when it is run, it produces an error:Scalar subquery produced more than one element

Input:

SELECT 
  (Select 
     pcr.repdte 
     from 
     usa_fdic_call_reports_1992.All_Reports_19921231_
     Performance_and_Condition_Ratios as PCR) as Quarter,
(SELECT 
     Round(PCR.lnlsdepr) 
     FROM 
   usa_fdic_call_reports_1992.All_Reports_19921231_Performance_
   and_Condition_Ratios as PCR) as NetLoansAndLeasesToDeposits,
(SELECT LD.IDdepsam 
    FROM usa_fdic_call_reports_1992.All_Reports_19921231_
      Deposits_Based_on_the_dollar250_000_Reporting_Threshold 
      AS LD) as DepositAccountsWithMoreThan250k

Output Scalar subquery produced more than one element

The output of the queries when they are run independently is below:

SELECT
  PCR.repdte as quarter
  FROM 
     usa_fdic_call_reports_1992.All_Reports_19921231_
     Performance_and_Condition_Ratios as PCR 

Output:

SELECT 
  Round(PCR.lnlsdepr) as NetLoansAndLeasesToDeposits
  FROM 
     usa_fdic_call_reports_1992.All_Reports_19921231_
     Performance_and_Condition_Ratios as PCR

Output:

SELECT LD.IDdepsam as DepositAccountsWithMoreThan250k
  FROM 
    usa_fdic_call_reports_1992.All_Reports_
    19921231_Deposits_Based_on_the_dollar250_000_
     Reporting_Threshold AS LD 

Output:

Answer 1

To fix, use ARRAY.

For example, this query works:

 SELECT 1 x, (SELECT y FROM UNNEST(SPLIT("1")) y) y

But this one will give you the stated error:

 SELECT 1 x, (SELECT y FROM UNNEST(SPLIT("1,2")) y) y

 "Scalar subquery produced more than one element"

And I can fix it with ARRAY(), that will produce nested repeated results:

 SELECT 1 x, ARRAY(SELECT y FROM UNNEST(SPLIT("1,2")) y) y

Or make sure to emit only one row, with LIMIT:

 SELECT 1 x, (SELECT y FROM UNNEST(SPLIT("1,2")) y LIMIT 1) y

Answer 2

I solved the problem by not using a subquery, and instead using JOIN

SELECT 
  pcr.cert as cert,
  pcr.name as NameOfBank,
  pcr.repdte as Quarter, 
  Round(PCR.lnlsdepr) as NetLoansAndLeasesToDeposits,
  LD.IDdepsam as DepositAccountsWithMoreThan250k
FROM 
    usa_fdic_call_reports_1992.All_Reports_19921231_Performance
    _and_Condition_Ratios as PCR
JOIN
    usa_fdic_call_reports_1992.All_Reports_19921231_Deposits_Based_
    on_the_dollar250_000_Reporting_Threshold AS LD
ON PCR.cert = LD.cert

Output:

Answer 3

Scalar subqueries cannot produce more than a single row. You are showing your scalar subqueries show a single column, and multiple rows. That -- by definition -- won't work.