How to solve Unrecognized name error in Google BigQuery

Problem 🔍

In Bigquery, Derived columns cannot be referred to in the same SELECT statement. You will get the below error message:

Unrecognized name: column at [1:20]

Solution 💡

To solve this error, try one of the below methods:

Method #1 Use the Original Column name instead of the ALIAS

Use this method If the column is a Simple ALIAS and there are no complex deriviations involved.  

Example-1:

For example, in the below query column year is directly aliased to y1, without any complex derivations

SELECT 
year as y1, 
(y1-2) as y2 
FROM projects.Dataset.table

you can rewrite this query using the column directly,

SELECT 
year as y1, 
(year-2) as y2 
FROM projects.Dataset.table

Example-2:

SELECT 
year as y1
FROM projects.Dataset.table 
where y1 = '2020' 
order by y1

you can rewrite this query using the column directly,

SELECT 
year as y1
FROM projects.Dataset.table 
where year = '2020' 
order by year

Method #2 Create a new select on top of the subquery and use the derived columns there

If the derived column is a result of complex transformations, you can create another SELECT clause on top of the existing query and move the derived columns to there, like shown below

 SELECT Id, 
 LAG(enddate,1) OVER (PARTITION BY client ORDER BY begindate) AS lag,
 (lag - 1) as diff
 FROM table

Needs to be rewritten as :

SELECT Id, lag, (lag - 1) as diff
FROM(
    SELECT Id
    LAG(enddate,1) OVER (PARTITION BY client ORDER BY begindate) AS lag
    FROM table
    ) AS t

More Troubleshooting tips ⚡

• The derived column alias can be anywhere (SELECT list, JOIN Conditions, WHERE Clause, ORDER BY Clause). Make sure you carefully look for them and handle it

Mike-Barn

posted on 21 Feb 19