double select in SQL query
<p>Writing a code with two select statements in big query. Not sure why its not recognizing the fields. Getting error <code>Field --&gt; 'd3.fiscal_week_begin_date'</code> not found; did you mean <code>'fiscal_week_begin_date'</code>? </p>

<pre><code>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 &gt;= d3.fiscal_week_begin_date
  AND test.created_timestamp &lt;= d3.fiscal_week_end_date;
</code></pre>