Resolved: Stored Procedure in Practice exam 2
Hi,
I am getting 0 rows returned when i run the code for the procedure from the practice exam 2. Not sure what i am doing wrong. Can i get feedback on it please? I had to obtain results using another code for the exam. Thanks. Screenshots attached.
Hi Minnerva Sasu!
Thanks for reaching out.
Can you please confirm that you have imported the data in the 'employees' database prior to creating the procedure? In other words, do you also obtain an empty data set if you ran the following query:
SELECT * FROM employees LIMIT 10;
If yes, then please upload the database as shown here:
https://learn.365datascience.com/courses/sql/loading-the-employees-database/Otherwise, please get back to us and we will take it from there. Thank you.
Looking forward to hearing from you.
Kind regards,
Martin
Thanks for you reply Martin. Just done as instructed and obtained results; screenshot attached.
Great, Minnerva! Thank you for letting us know.
Good luck and please feel free to post another question should you encounter any difficulties. Thank you.
Best,
Martin
Hi Martin,
I actually meant i got results for what you asked me the run: the select from employees with a limit of 10.
Sorry for the confusion
I am still unable to get results from the stored procedure. Not sure if the problem is from the 'AND' in the where clause:
WHERE
e.emp_no = p_emp_no
AND
de.from_date = (SELECT
MAX(from_date)
FROM
dept_emp
WHERE
e.emp_no = p_emp_no);
when i take out the statement after the "AND", i seem to get results; however, there could be 2 or more depending on the employee and i'm not able to tell which department is associated to the MAX(from_date).
Hi Minnerva!
Thank you for getting back to this thread.
You have referred to the employees table through the alias e in the second condition in the WHERE clause of the outer query (... WHERE e.emp_no = p_emp_no). Thus, although your code abides by the SQL syntax (which is why you are not receiving an error message from MySQL), you are referring to a table at a higher level (i.e. that has been mentioned in the outer query). Instead, you need to refer to the employees number field (emp_no) from the table you have referred to in the subquery (which is dept_emp). Please observe the condition provided in the WHERE clause in the subquery (i.e. this is the WHERE clause of a lower level, the inner query):
AND de.from_date = (SELECT
MAX(from_date)
FROM
dept_emp
WHERE
emp_no = p_emp_no)
Once you've done that, your references should work properly and you should obtain the desired output.
Hope this helps.
Best,
Martin
Hi Martin,
I am getting results now. Thank you!
Great! Thank you very much for letting us know!
Good luck and please feel free to post another question should you encounter any difficulties. Thank you.
Best,
Martin