Reputation: 11
LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
Why do I get the above BQ error msg when running the below query? I'm not using any left outer join. Thanks!
SELECT *
FROM
(SELECT
*
, (select sum(transaction_amount) from `analytics-111.tmp.coll` where transaction_date_est <= mq.transaction_date_est) RunningTotal
FROM `analytics-111.tmp.coll` mq) mq2
WHERE mq2.RunningTotal < 1000 ```
Upvotes: 0
Views: 56
Reputation: 1271231
The subquery is just another way of expressing a left join
. The optimizer recognizes the issue after rewriting the query -- and the error message doesn't know if the original code is a LEFT JOIN
or correlated subquery. The error message could be fixed.
Happily, you can calculate this using a cumulative sum:
SELECT mq.*,
SUM(mq.transaction_amount) OVER (ORDER BY transaction_amount) as RunningTotal
FROM `analytics-111.tmp.coll` mq
QUALIFY mq.RunningTotal < 1000;
Note that this uses the new QUALIFY
clause which allows you to filter on window functions.
Upvotes: 1