BigQuery: Unrecognized name for derived field

0
votes

I've 2 tables 1&2. Inside table1, I've created a derived field "actualoutput" against which I'm using join with table-2 with "newgroup" as a connector, to get field "finalgroup" as shown below.

Since the derived field cannot be used in the same select, I used foll. query by using the derived field in the outer query. But this is giving error:

Unrecognised name: actualoutput

The query:

select source, detail, CONCAT(IFNULL(source, ''), "-", IFNULL(detail, '')) AS actualoutput, newgroup, finalgroup 
from (
SELECT source, detail from `table1`)
left join (select newgroup,finalgroup from `table2`)
on actualoutput=newgroup

Refer image: columns highlighted in yellow are used as a key.

enter image description here

Can someone please help me with the solution.

1
google-bigquery

1 Answers

1
votes

I got the answer. Basically I was using incorrect query by using table-2 columns in the same outerquery along with derived field "actualoutput". Foll. query gives output as expected:

select source, detail, CONCAT(IFNULL(Source, ''), "-", IFNULL(Detail, '')) AS actualoutput, newgroup,finalgroup
from (
select source, detail, CONCAT(IFNULL(Source, ''), "-", IFNULL(Detail, '')) AS actualoutput
from (
SELECT source, detail from `table1`))
left join (select newgroup,finalgroup from `table2`)
on actualoutput=newgroup