Home » SQL & PL/SQL » SQL & PL/SQL » what is the issue in my query (Toad 10.6.1)
what is the issue in my query [message #624327] Sun, 21 September 2014 13:26 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 :

AND e.deptno =10


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.
Re: what is the issue in my query [message #624331 is a reply to message #624329] Sun, 21 September 2014 14:08 Go to previous messageGo to next message
lvrvln
Messages: 5
Registered: August 2014
Junior Member
Thank you Barbara/Lalit Kumar

[Updated on: Sun, 21 September 2014 14:08]

Report message to a moderator

Re: what is the issue in my query [message #624444 is a reply to message #624331] Mon, 22 September 2014 15:44 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Why even have the subquery. It is totally unnecessary.

SELECT empno, sal, dname, e.deptno
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
and sal > 2000
and e.deptno = 10;

[Updated on: Mon, 22 September 2014 15:44]

Report message to a moderator

Re: what is the issue in my query [message #624520 is a reply to message #624444] Tue, 23 September 2014 06:18 Go to previous messageGo to next message
lvrvln
Messages: 5
Registered: August 2014
Junior Member
Yes, You are right. Just learning pl/sql. Thanks
Re: what is the issue in my query [message #624525 is a reply to message #624520] Tue, 23 September 2014 06:44 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
lvrvln wrote on Tue, 23 September 2014 16:48
Just learning pl/sql.


Your question is about SQL, not PL/SQL. They are not the same Wink
Previous Topic: Data in AM/PM
Next Topic: Query to find duplicate rows from table
Goto Forum:
  


Current Time: Thu Mar 28 15:20:38 CDT 2024