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

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

enter image description here

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: enter image description here

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

Output: enter image description here

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: enter image description here

3 answers

  • answered 2019-03-13 17:34 The Impaler

    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.

  • answered 2019-03-13 23:59 user2755660

    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: enter image description here

  • answered 2019-03-14 04:14 Felipe Hoffa

    To fix, use ARRAY.

    For example, this query works:

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

    enter image description here

    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
    

    enter image description here

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

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

    enter image description here