CODEWITHSUNDEEP
sqlgoogle-bigquerybq
user15067132
user15067132

Reputation: 11

Why do I get a LEFT OUTER JOIN error msg when i'm not even using a left join?

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions