In the solution of the second problem, when we select calendar_year, why we use a subquery in the from statement instead of using year(hire_date) in the select statement. I tried it and didn’t work.
Thanks for reaching out.
Can you please support your question with the exact query you’ve executed? This will help us assist you better.
Looking forward to your answer.
This is the query i’ve executed
YEAR(e.hire_date) AS calendar_year,
WHEN YEAR(dm.to_date) >= calendar_year AND YEAR(dm.from_date) <= calendar_year THEN 1
END AS active
t_departments d ON dm.dept_no = d.dept_no
t_employees e ON dm.emp_no = e.emp_no
ORDER BY dm.emp_no, calendar_year;
Thank you very much for your reply.
What is important to remember is that we cannot set alias names in the field list and use them in the calculation/formula for defining another field. This must happen, say, at a different level in our query.
Since we want to use the calendar_year value in the CASE statement, we need to have defined it with the FROM clause (as opposed to the SELECT statement). Hence, the following subquery is very important to be added in the FROM clause.
YEAR(hire_date) AS calendar_year
GROUP BY calendar_year) e
Please add it and retry running your query.
Hope this helps.
Thank you so much Martin
You are very welcome!