CODEWITHSUNDEEP
google-bigquery
Feltie
Feltie

Reputation: 151

BigQuery - Cannot join on repeated field

Im trying to create a table that is 1 column with each row being a new date between 2 separate dates. The query works fine until I add a where clause that contains a subquery ie. NOT IN (SELECT ....). It works fine if I do something like NOT IN (TIMESTAMP('xyz')).

I keep getting an error saying "Cannot join on repeated field t2.f0__group.SomeDate"

I have no clue why this is happening. Also Im fairly new to BQ so if there is an easier way to do this please let me know. Thanks

SELECT SomeDate FROM
(
  SELECT DATE_ADD(Day, i, "DAY") SomeDate
  FROM
  (
    SELECT '2020-01-03' Day
  ) T1
  CROSS JOIN
  (
    SELECT
     POSITION(
       SPLIT(
         RPAD('', DATEDIFF('2020-01-30','2020-01-03') * 2, 'a,'))) i 
    FROM
    (
      SELECT NULL
    )
  ) T2
)
WHERE SomeDate NOT IN (SELECT OtherDate FROM 
  (
    SELECT TIMESTAMP('2020-01-04 00:00:00 UTC') AS OtherDate
  ),
  (
    SELECT TIMESTAMP('2020-01-06 00:00:00 UTC') AS OtherDate
  ),
  (
    SELECT TIMESTAMP('2020-01-08 00:00:00 UTC') AS OtherDate
  )
)

Upvotes: 0

Views: 1461

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172964

I suggest to start over from scratch using below example
I think it does exactly what you are trying to achieve with probably minor adjustments 

SELECT SomeDate
FROM (
  SELECT 
    DATE(DATE_ADD(TIMESTAMP('2020-01-03'), pos - 1, "DAY")) AS SomeDate
  FROM (
     SELECT ROW_NUMBER() OVER() AS pos, *
     FROM (FLATTEN((
       SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP('2020-01-30'), TIMESTAMP('2020-01-03')), '.'),'') AS h
       FROM (SELECT NULL)),h
    ))
  )
) a
LEFT JOIN (
SELECT OtherDate FROM
    (SELECT '2020-01-04' AS OtherDate),
    (SELECT '2020-01-06' AS OtherDate),
    (SELECT '2020-01-08' AS OtherDate)
) b
ON b.OtherDate = a.SomeDate
WHERE b.OtherDate IS NULL

Upvotes: 1

Related Questions