CODEWITHSUNDEEP
google-bigquery
Ido Barash
Ido Barash

Reputation: 5122

Big query "Where inner select" - can't join or union

I get:

(L1:268): JOIN (including semi-join) and UNION ALL (comma, date range) may not be combined in a single SELECT statement. Either move the UNION ALL to an inner query or the JOIN to an outer query.

query:

SELECT   a                             AS a, 
         Count( DISTINCT b, 999999)    AS b, 
FROM     [dataset.partition_20121216], 
         [dataset.partition_20130115], 
         [dataset.partition_20130214] 
WHERE    created BETWEEN Timestamp('2013-01-05 00:00:00') AND      Timestamp ('2013-02-15 23:59:59.999999') 
AND      id IN  ( 
                SELECT id 
                FROM   [dataset.partition_20121216], 
                       [dataset.partition_20130115], 
                       [dataset.partition_20130214] 
                WHERE  created BETWEEN Timestamp('2013-01-05 00:00:00') AND    Timestamp ('2013-02-15 23:59:59.999999') 
                AND    name = 'G’ 
    ) 
GROUP each a 
ORDER BY a

What did I do wrong?

thanks.

Upvotes: 4

Views: 1946

Answers (2)

CharlieNoTomatoes
CharlieNoTomatoes

Reputation: 521

The way I was able to go around this same issue was to use the subqueries in joins, rather than the where in syntax. Something along the lines of

SELECT
  a,
  COUNT(b)
FROM (
  SELECT
    id,
    a,
    b
  FROM
    [dataset.partition_20121216],
    [dataset.partition_20130115],
    [dataset.partition_20130214]
  WHERE
    created BETWEEN TIMESTAMP('2013-01-05 00:00:00')
    AND Timestamp ('2013-02-15 23:59:59.999999') ) a
JOIN (
  SELECT
    DISTINCT id
  FROM
    [dataset.partition_20121216],
    [dataset.partition_20130115],
    [dataset.partition_20130214]
  WHERE
    created BETWEEN TIMESTAMP('2013-01-05 00:00:00')
    AND Timestamp ('2013-02-15 23:59:59.999999')
    AND name = 'G' ) b
ON
  b.id = a.id
GROUP BY
  a
ORDER BY
  a

Upvotes: 0

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

This is limitation of BigQuery SQL syntax, I would change it to

SELECT   a                             AS a, 
         Count( DISTINCT b, 999999)    AS b, 
FROM     
  (SELECT * FROM 
     [dataset.partition_20121216], 
     [dataset.partition_20130115], 
     [dataset.partition_20130214]) 
WHERE    created BETWEEN Timestamp('2013-01-05 00:00:00') AND      Timestamp ('2013-02-15 23:59:59.999999') 
AND      id IN  ( 
                SELECT id 
                FROM   [dataset.partition_20121216], 
                       [dataset.partition_20130115], 
                       [dataset.partition_20130214] 
                WHERE  created BETWEEN Timestamp('2013-01-05 00:00:00') AND    Timestamp ('2013-02-15 23:59:59.999999') 
                AND    name = 'G’ 
    ) 
GROUP each a 
ORDER BY a

Upvotes: 4

Related Questions