user2472382 user2472382 - 17 days ago 5
SQL Question

double select in SQL query

Writing a code with two select statements in big query. Not sure why its not recognizing the fields. Getting error

Field --> 'd3.fiscal_week_begin_date'
not found; did you mean
'fiscal_week_begin_date'
?

SELECT
*
FROM
[acquisition.acquisition_program_cost_summary] as test,
(
SELECT
d.fiscal_week_begin_date AS fiscal_week_begin_date,
d.fiscal_week_end_date AS fiscal_week_end_date
FROM
datamart.date_dim as d
WHERE
d.week_of_fiscal_year =30) as d3
WHERE
test.created_timestamp >= d3.fiscal_week_begin_date
AND test.created_timestamp <= d3.fiscal_week_end_date;

Answer

In BigQuery comma (,) does not represent JOIN but rather UNION ALL
So, most likely you meant to do JOIN like below
Please note you should switch to BigQuery Standard SQL

SELECT *
FROM acquisition.acquisition_program_cost_summary as test
JOIN (
  SELECT
    d.fiscal_week_begin_date AS fiscal_week_begin_date,
    d.fiscal_week_end_date AS fiscal_week_end_date
  FROM datamart.date_dim as d
  WHERE d.week_of_fiscal_year = 30
) as d3
ON test.created_timestamp >= d3.fiscal_week_begin_date
AND test.created_timestamp <= d3.fiscal_week_end_date
Comments