FLATTEN with TABLE_DATE_RANGE

Question

I have a range of tables in a dataset and need to query all of them while FLATTENing one of the repeated records. For example, a query like the following works fine:

 SELECT
   date,
   customDimensions.value AS customDimension,
   hits.page.pagePath AS pagePath
FROM
   (FLATTEN( [<projectId>:<datasetId>.ga_sessions_20130910] ,
   customDimensions)) 
WHERE
   hits.page.pagePath CONTAINS '/helmets' 
   AND customDimensions.index IN (1,2,3)

However, I am having trouble FLATTENing while using table wildcards. Can someone help me out with the syntax? Is it possible to use FLATTEN with TABLE_DATE_RANGE?

SELECT
   date,
   customDimensions.value AS customDimension,
   hits.page.pagePath AS pagePath
FROM
   (FLATTEN (TABLE_DATE_RANGE ([<project>:<dataset>.ga_sessions_],
   TIMESTAMP('2013-09-10'),
   TIMESTAMP ('2014-06-10'))),
   customDimensions) 
WHERE
   hits.page.pagePath CONTAINS '/helmets' 
   AND customDimensions.index IN (1,2,3)

Thanks, Shayan

Answer 1

According to reference manual, the FLATTEN syntax is:

...(FLATTEN ([project_name:]datasetId.tableId, flattenField))..
...(FLATTEN (subselect_clause, flattenField))..

Based on this, I would try putting a subselect clause within the FLATTEN statement like so:

SELECT
date,
customDimensions.value AS customDimension,
hits.page.pagePath AS pagePath
FROM
FLATTEN(
(SELECT date, customDimensions, hits FROM TABLE_DATE_RANGE ([<project>:<dataset>.ga_sessions_],
TIMESTAMP('2013-09-10'),TIMESTAMP ('2014-06-10')))
, customDimensions)
WHERE
hits.page.pagePath CONTAINS '/helmets' 
AND customDimensions.index IN (1,2,3)