All geek questions in one place

BigQuery JOIN Error

I try to solve this error for hours without any luck, its on request, which I ran for several weeks without problems, but suddenly I see this error:

Error: the ON clause must be AND = matching one field name from each table with all field names with a table name prefix.

The request is formatted as follows:

SELECT S.av AS av, S.dm AS dm, t, gn FROM [dataset.cTable] JOIN EACH (SELECT id, av, dm FROM [dataset.sTable]) AS S ON S.id = sid AND (t == 'type1' OR t == 'type2') GROUP EACH BY av, dm, t, gn;

Any help would be greatly appreciated.

+5
source share
2 answers

The sentence (t == 'type1' OR t == 'type2') not a join condition, it is a where clause. If you change your request to:

 SELECT S.av AS av, S.dm AS dm, Ct, C.gn FROM [dataset.cTable] C JOIN EACH (SELECT id, av, dm FROM [dataset.sTable]) AS S ON S.id = sid WHERE (Ct == 'type1' OR Ct == 'type2') GROUP EACH BY S.av, S.dm, Ct, C.gn;

he should work.

+2
source

Allegedly adding to aliases will solve the problem. I also don't think that a subquery is needed:

 SELECT S.av AS av, S.dm AS dm, ct, c.gn FROM [dataset.cTable] as c JOIN EACH [dataset.sTable] AS S ON S.id = c.sid WHERE ct in ('type1', 'type2') GROUP EACH BY av, dm, t, gn;

each may not be needed, but I'm not too familiar with Google BigQuery.

0
source

Source: https://habr.com/ru/post/1202456/

More articles:


All Articles