Thanks for the question, neha.
Asked: December 04, 2016 - 3:47 pm UTC
Last updated: December 05, 2016 - 11:34 am UTC
Version: oracle 10 g
Viewed 1000+ times
You Asked
Hello
I am facing problem with alias name in the joins. Pleae help me out.
Below is the example
select y.employee_id from
((select employee_id,department_id,manager_id from employees)a
join
(select department_id,manager_id from departments)d
on a.manager_id=d.manager_id
and a.department_id=d.department_id)y
The above query is not working if i keep alias name 'y' and trying to pull an particular column.In other words structure of my query is
select y.* from ( a join d )y;[Not Working]
If i remove the alias name and keep * instead, it works.In other words if my query structure is
select * from(a join d)[Working]
Thanks in advance
and Chris said...
You should have a select in your inner query:
SQL> select y.employee_id
2 from (
3 ( select employee_id,department_id,manager_id from hr.employees) a
4 join
5 ( select department_id,manager_id from hr.departments where department_id = 100) d
6 on a.manager_id =d.manager_id
7 and a.department_id=d.department_id
8 ) y;
) y
*
ERROR at line 8:
ORA-00933: SQL command not properly ended
SQL>
SQL> select y.employee_id
2 from (
3 select * from
4 ( select employee_id,department_id,manager_id from hr.employees) a
5 join
6 ( select department_id,manager_id from hr.departments where department_id = 100) d
7 on a.manager_id =d.manager_id
8 and a.department_id=d.department_id
9 ) y;
EMPLOYEE_ID
-----------
109
110
111
112
113
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment