what is the issue in my query [message #624327] |
Sun, 21 September 2014 13:26 |
|
lvrvln
Messages: 5 Registered: August 2014
|
Junior Member |
|
|
Schema HR
Hi Folks,
when i ran below query i am getting error ( invalid identifier).
SELECT *
FROM (SELECT empno,
sal, dname, e.deptno
FROM emp e, dept d
WHERE e.deptno = d.deptno)
WHERE sal > 2000
AND e.deptno =10
ORA-00904: "E"."DEPTNO": invalid identifier
if i give alias to e.deptno , it is working fine
SELECT *
FROM (SELECT empno,
sal, dname, e.deptno as dno
FROM emp e, dept d
WHERE e.deptno = d.deptno)
WHERE sal > 2000
AND dno = 10;
i am not sure why my 1st query failed, i just gave e.deptno =10 as a condition
Edited by Lalit : Added code tags
[Updated on: Sun, 21 September 2014 13:34] by Moderator Report message to a moderator
|
|
|
Re: what is the issue in my query [message #624328 is a reply to message #624327] |
Sun, 21 September 2014 13:39 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The table alias e only applies within your inline view (sub-query in the from clause). You can reference deptno from the outer query without an alias, just as you did sal or you can create an alias for your inline view and use that. I have demonstrated both below.
Also, please note that a kind moderator, Lalit added code tags to your original post. Please use the code tags yourself in the future, so that your code is formatted and easier to read.
SCOTT@orcl12c> SELECT *
2 FROM (SELECT empno, sal, dname, e.deptno
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno)
5 WHERE sal > 2000
6 AND deptno = 10
7 /
EMPNO SAL DNAME DEPTNO
---------- ---------- -------------- ----------
7782 2450 ACCOUNTING 10
7839 5000 ACCOUNTING 10
2 rows selected.
SCOTT@orcl12c> SELECT *
2 FROM (SELECT empno, sal, dname, e.deptno
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno) alias_for_inline_view
5 WHERE sal > 2000
6 AND alias_for_inline_view.deptno = 10
7 /
EMPNO SAL DNAME DEPTNO
---------- ---------- -------------- ----------
7782 2450 ACCOUNTING 10
7839 5000 ACCOUNTING 10
2 rows selected.
|
|
|
Re: what is the issue in my query [message #624329 is a reply to message #624327] |
Sun, 21 September 2014 13:40 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
The error is thrown because the outer query doesn't understand the predicate :
The inner query would have column name as "deptno", but you are referencing it in the outer query as "e.deptno". In your second query, when you specified an alias for the deptno column, the resultset of inner query has the column name as "dno" which you are referencing in the predicate of outer query.
Alternatively, you can give an alias to the resultset of the inner query, and use the alias.column_name to refer the columns in the predicate of outer query.
|
|
|
|
|
|
|