CODEWITHSUNDEEP
sqlgoogle-bigquery
W. Mooi
W. Mooi

Reputation: 119

Error: Unrecognized name: Google BigQuery

I am new to SQL and i'm trying to make 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 giving the error "Error: Unrecognized name: lag at ....."

I really don't understand what's wrong with the code; in the dataset preview I see that the new column "lag" is succesfully created (when I remove the date_diff codepiece). So why is "lag" an unrecognized name?

Upvotes: 2

Views: 18256

Answers (3)

iam_william
iam_william

Reputation: 1

As Johnny Schneider Antoine says above, the query only works when you add aliases at FROM and INNER JOIN as follows:

SELECT
    employees.name AS employee_name,
    employees.role AS employee_role,
    departments.name AS department_name
FROM
    `coursera-hands-on-370023.employee_data.employees` AS employees --only works when you add the employees alias
INNER JOIN
    `coursera-hands-on-370023.employee_data.departments` AS departments -- only works when you add the departments alias
     ON
     employees.department_id = departments.department_id

However, the query works fine without the aliases when I uploaded the tables to my PostgreSQL database using Valentina Studio!

Upvotes: 0

Johnny Schneider Antoine
Johnny Schneider Antoine

Reputation: 11

Well, I rewrote the code as follows it works:

SELECT
emp.name AS employee_name,
emp.role AS employee_role,
dept.name AS department_name
FROM
`third-project-361918.employee_data.employees`AS emp
INNER JOIN
`third-project-361918.employee_data.departments` AS dept
ON
emp.department_id = dept.department_id

Upvotes: 1

Nishant Gupta
Nishant Gupta

Reputation: 3656

Try This one:

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

Upvotes: 4

Related Questions