I am trying to filter an alias column, which I know it wont work in the WHERE clause. I need to filt-CodePudding

select department_id, last_name, round(((sysdate-hire_date)/30)/12, 0) as YEARS
from employees
where YEARS >15;

CodePudding user response：

You can't refer to the alias in the WHERE clause. One option is to simply repeat the entire round expression:

SELECT department_id, last_name,
       ROUND(((SYSDATE - hire_date) / 30) / 12, 0) AS YEARS
FROM employees
WHERE ROUND(((SYSDATE - hire_date) / 30) / 12, 0) > 15;

CodePudding user response：

One option is to repeat the round expression and also you can use DATEDIFF which is another option.

SELECT department_id, last_name,
       ROUND(((SYSDATE - hire_date) / 30) / 12, 0) AS YEARS
FROM employees 
Where  DATEDIFF(year, hire_date, getdate()) > 15