I am trying to do JOIN on two columns from two different tables (one of them is a view) in Google BigQuery. I have tried this numerous ways, but have received this error the most consistently:
我试图在Google BigQuery中的两个不同的表(其中一个是视图)中的两列上进行JOIN。我尝试过这么多种方法,但是最一致地收到了这个错误:
invalidQuery: 2.1 - 0.0: JOIN cannot be applied directly to a table union or to a table wildcard function. Consider wrapping the table union or table wildcard function in a subquery (e.g., SELECT *).
Here is my SQL (legacy) query:
这是我的SQL(遗留)查询:
SELECT
blp_today.beta_key,
blp_today.px_last,
blp_today.eqy_weighted_avg_px,
blp_today.created_date,
blp_today.security_ticker,
ciq_company_stg.ticker,
ciq_company_stg.ciq
FROM
[fcm-dw:acquisition_bloomberg.blp_today],
[fcm-dw:acquisition_ciq]
JOIN
blp_today.security_ticker AS ticker
ON
blp_today.security_ticker = ciq_company_stg.ticker
LIMIT 1000
Any help would be much appreciated.
任何帮助将非常感激。
2 个解决方案
#1
1
I think you either want something like this:
我想你要么想要这样的东西:
SELECT * FROM(SELECT
beta_key,
px_last,
eqy_weighted_avg_px,
created_date,
security_ticker,
FROM
[fcm-dw:acquisition_bloomberg.blp_today],
[fcm-dw:acquisition_ciq] ) as a
JOIN
blp_today.security_ticker AS ticker
ON
a.security_ticker = ciq_company_stg.ticker
LIMIT 1000
//edit: I kind of missed earlier that the table that you are joining (after your join statement) does not actually seem to be a table. Are you trying to join or to union these two tables: [fcm-dw:acquisition_bloomberg.blp_today] and [fcm-dw:acquisition_ciq] ? And is the latter even a table? Your code seems to indicate that there is another table named: [fcm-dw:acquisition_ciq.ciq_company_stg]?
//编辑:我之前有点想念你加入的表(在你的join语句之后)实际上似乎不是一个表。你是想加入还是联合这两个表:[fcm-dw:acquisition_bloomberg.blp_today]和[fcm-dw:acquisition_ciq]?而后者甚至是一张桌子?你的代码似乎表明还有另一个名为:[fcm-dw:acquisition_ciq.ciq_company_stg]的表?
#2
0
First wrap your union into a sub select then join the result
首先将您的联合包装到子选择中然后加入结果
select ...
FROM
(select * from
[fcm-dw:acquisition_bloomberg.blp_today],
[fcm-dw:acquisition_ciq] ) t
JOIN
blp_today.security_ticker AS ticker
#1
1
I think you either want something like this:
我想你要么想要这样的东西:
SELECT * FROM(SELECT
beta_key,
px_last,
eqy_weighted_avg_px,
created_date,
security_ticker,
FROM
[fcm-dw:acquisition_bloomberg.blp_today],
[fcm-dw:acquisition_ciq] ) as a
JOIN
blp_today.security_ticker AS ticker
ON
a.security_ticker = ciq_company_stg.ticker
LIMIT 1000
//edit: I kind of missed earlier that the table that you are joining (after your join statement) does not actually seem to be a table. Are you trying to join or to union these two tables: [fcm-dw:acquisition_bloomberg.blp_today] and [fcm-dw:acquisition_ciq] ? And is the latter even a table? Your code seems to indicate that there is another table named: [fcm-dw:acquisition_ciq.ciq_company_stg]?
//编辑:我之前有点想念你加入的表(在你的join语句之后)实际上似乎不是一个表。你是想加入还是联合这两个表:[fcm-dw:acquisition_bloomberg.blp_today]和[fcm-dw:acquisition_ciq]?而后者甚至是一张桌子?你的代码似乎表明还有另一个名为:[fcm-dw:acquisition_ciq.ciq_company_stg]的表?
#2
0
First wrap your union into a sub select then join the result
首先将您的联合包装到子选择中然后加入结果
select ...
FROM
(select * from
[fcm-dw:acquisition_bloomberg.blp_today],
[fcm-dw:acquisition_ciq] ) t
JOIN
blp_today.security_ticker AS ticker