17 October 2018 · Ryan-Dallas ·       Add to Favorites   Report

Google BigQuery Error: Unrecognized name

With BigQuery, you cannot use the derived column within the same select clause. Instead, you will need to build another select clause on top of existing select and use the derived column for further calculations. Look at the below example:

You have a query in Google BigQuery that looks like this:

 SELECT client, begindate, enddate, 
 LAG(enddate,1) OVER (PARTITION BY client ORDER BY begindate, 
 client) AS lag,
 ROUND(DATE_DIFF(DATE(begindate), lag, DAY)) as diff
 FROM
 db LIMIT 100;

But it's throwing the error "Error: Unrecognized name: lag at ....."

Solution:

With BigQuery, you cannot use the derived column within the same select. You need to build another select on top of existing select where the derived field is present. Like given below,

SELECT client,begindate, enddate,lag,
ROUND(DATE_DIFF(DATE(begindate), lag, DAY)) as diff
FROM (
      SELECT client, begindate, enddate, 
      LAG(enddate,1) OVER (PARTITION BY client ORDER BY begindate, 
      client) AS lag, 
      FROM
      db LIMIT 100;
     ) AS t

Ryan-Dallas

posted on 17 October 2018

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied