Error: Syntax error: Expected ")" but got end of statement case statements in subquery

Question

I'm having an issue figuring out how I should be ending this query because I keep getting an error in BigQuery.

It's a relatively large query with several unions all following pretty much the same syntax. When I go to end the query following the subquery I am getting an error.

Error: Syntax error: Expected ")" but got end of statement at [133:54]

All of the unions follow the same syntax—just from separate tables

UNION ALL
  SELECT
    report_date,
    device_category,
    data_source,
    source,
    medium,
    LOWER(campaign_name) AS campaign_name,
    conversion_type,
    Brand,
    goal_completion_1,
    impressions,
    clicks,
    cost,
    conversions,
    profile
  FROM (
    SELECT
      report_date,
      device_category AS device,
      data_source,
      CASE
        WHEN data_source = 'Google Ads' THEN 'google'
        WHEN data_source = 'Adroll' THEN 'adroll'
        WHEN data_source = 'Facebook Ads' THEN 'facebook'
        WHEN data_source = 'Bing Ads' THEN 'bing'
        ELSE NULL
      END AS source,
      CASE
        WHEN data_source = 'Google Ads' THEN 'cpc'
        WHEN data_source = 'Adroll' THEN 'display'
        WHEN data_source = 'Facebook Ads' THEN 'paid_social'
        WHEN data_source = 'Bing Ads' THEN 'cpc'
        ELSE NULL
      END AS medium,
      0 AS goal_completion_1,
      0 AS impressions,
      0 AS clicks,
      0 AS cost,
      conversions,
      profile
    FROM
      `table`
  )

I have a similar query to this except it ends with a where statement and it seems to run fine- but when I add anything after the from I still get the same error.

WHERE conversion_type <> 'Calls from ads'

justbeez justbeez · Accepted Answer · 2019-07-12T16:29:11

It's difficult to be sure where the issue is without seeing the whole query.

More than likely, there's a missing closing parenthesis at the end of one of the closing subqueries, causing the UNION to be viewed as inside that subquery. It's also possible that there's a parentheses before the entire query, at which point it isn't getting closed.

I've found that I can get more helpful error messages in large UNION queries by wrapping each UNION'd query in parentheses, like so:

(
  SELECT ...
)
UNION ALL
(
  SELECT ...
)
UNION ALL
(
  SELECT ...
)

At minimum, this has the effect of causing unclosed character warnings to typically fire at the line number ending each group, versus the end of the query.

While Joakim is correct that you're selecting more columns than are available, that is not the cause for this error message.

Error: Syntax error: Expected ")" but got end of statement case statements in subquery

1 Answers