Nested queries WITH bigquery

Question

I'm trying to figure why this query doesn't work

    WITH
  WEEKLY_FILTERS AS (
  SELECT
    Make,
    Country,
    Year,
    Month
  FROM
    table_w ),
  MONTHLY AS (
  SELECT
    *
  FROM
    table_m MT
  WHERE
    (MT.Make,
      MT.Country,
      MT.Year,
      MT.Month) NOT IN WEEKLY_FILTERS)



SELECT
  *
FROM
  table_w
UNION ALL
  MONTHLY

But I always get an error like this Syntax error: Expected "(" or keyword UNNEST but got identifier "WEEKLY_FILTERS"

Any help please

P.S : I had the original query working, but the use of "WITH" is only it can work as a materialised view because BigQuery doesn't accept nested queries in them.

Regards,

Daniel Zagales · Answer

The NOT IN operator requires either an expression / list of expressions, subquery, or an unnest value. https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#in_operators

In your case try changing the NOT IN to

NOT IN
(SELECT (Make,
    Country,
    Year,
    Month)
  FROM WEEKLY_FILTERS
)

Nested queries WITH bigquery

1 Answers