CODEWITHSUNDEEP
google-bigquery
FZF
FZF

Reputation: 915

BigQuery union is failing when trying to union 2 subqueries that include join operation

We are trying to union 2 subqueries and getting error. Here is the query:

select *
from
(select  TRANS_SEQ, TRANS_TS
from [X.ls_transaction] ls
left join each (select * from [X.resident_s] where date(eff_end_ts) = date('9999-12-31')) rs on ls.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) a,
(select TRANS_SEQ, TRANS_TS,
from [X.ld_transaction] ld
left join each (select * from [X.resident_d] where date(eff_end_ts) = date('9999-12-31')) rs on ld.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) b
limit 1;

Here is the error it returns:

Error: Union results in ambiguous schema. [TRANS_SEQ] is ambiguous and is aliasing multiple fields. Aliased fields: ld.TRANS_SEQ,ls.TRANS_SEQ,

If I take both of the joins from the subquery out then the union works.
Is this a bug?

Thanks

Upvotes: 3

Views: 1096

Answers (2)

ornachshon
ornachshon

Reputation: 105

Try to give second of the two "TRANS_SEQ" variables a different name and use the coalesce function. Below is my approach to solving this problem:

select coalesce (TRANS_SEQ, TRANS_SEQ_b)
from
(select  TRANS_SEQ, TRANS_TS
from [X.ls_transaction] ls
left join each (select * from [X.resident_s] where date(eff_end_ts) = date('9999-12-31')) rs on ls.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) a,
(select TRANS_SEQ as TRANS_SEQ_b , TRANS_TS,
from [X.ld_transaction] ld
left join each (select * from [X.resident_d] where date(eff_end_ts) = date('9999-12-31')) rs on ld.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) b
limit 1;

I have tested it and it works.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Most likely you have TRANS_SEQ field in X.resident_d and X.resident_s which introduces ambiguity.
You should do something like below

select *
from
(select ls.TRANS_SEQ as TRANS_SEQ, TRANS_TS
from [X.ls_transaction] ls
left join each (select * from [X.resident_s] where date(eff_end_ts) = date('9999-12-31')) rs on ls.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) a,
(select ld.TRANS_SEQ as TRANS_SEQ, TRANS_TS,
from [X.ld_transaction] ld
left join each (select * from [X.resident_d] where date(eff_end_ts) = date('9999-12-31')) rs on ld.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) b
limit 1;

Upvotes: 2

Related Questions