Home » SQL & PL/SQL » SQL & PL/SQL » Error "ORA-00904" while using subquery. (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Error "ORA-00904" while using subquery. [message #574271] Tue, 08 January 2013 08:11 Go to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I'm facing an issue "ORA-00904". Below is the test case. Both the queries are different. I'm only focussed to find out the reason.

CREATE TABLE ACCT
( 
	ACCTNBR 	NUMBER(10)
) ;

INSERT INTO ACCT VALUES( 5 ) ;

SELECT * 
FROM ACCT A 
WHERE A.ACCTNBR = 5
AND EXISTS ( SELECT 1 
			 FROM ACCT B 
			 WHERE B.ACCTNBR = A.ACCTNBR 
			   AND EXISTS ( SELECT 2 
							FROM ACCT C 
							WHERE C.ACCTNBR = A.ACCTNBR ) ) ;

SELECT *
FROM ACCT A
WHERE A.ACCTNBR = 5
AND 2 = ( SELECT MYTEST 
		  FROM ( SELECT 2 MYTEST 
				 FROM ACCT B 
				 WHERE B.ACCTNBR = A.ACCTNBR ) ) ;

In first query I'm able to refer to table alias A but in second query I'm not able to refer it. The only difference is that in second query I've not used outer table at second level. Is it the desired behaviour?

Thanks & Regards
Manoj
Re: Error "ORA-00904" while using subquery. [message #574286 is a reply to message #574271] Tue, 08 January 2013 10:24 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
Seemes to be expected behavior, at least for Tom Kyte
Quote:

ANSI SQL has table references (correlation names) scoped to just one level deep

Re: Error "ORA-00904" while using subquery. [message #574288 is a reply to message #574286] Tue, 08 January 2013 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59180
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not only for Tom Kyte for anyone.
It is the standard. Full stop.

Regards
Michel
Re: Error "ORA-00904" while using subquery. [message #574317 is a reply to message #574288] Tue, 08 January 2013 23:44 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
What could be the work around then?
Re: Error "ORA-00904" while using subquery. [message #574327 is a reply to message #574317] Wed, 09 January 2013 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59180
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Error "ORA-00904" while using subquery. [message #574453 is a reply to message #574327] Thu, 10 January 2013 09:51 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
Michel,
Could you elaborate on levels deep please? Can you explain why the first query is one level deep and the second two?
Op has actually provided the test case and the result has no importance in this case.

Re: Error "ORA-00904" while using subquery. [message #574456 is a reply to message #574453] Thu, 10 January 2013 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59180
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually the syntax you use shows 2 level deep but Oracle rewrites the query so there is no reference to the column as it is a constant:
SQL> @xpl3p

0 rows deleted.

  2  SELECT * 
  3  FROM ACCT A 
  4  WHERE A.ACCTNBR = 5
  5  AND EXISTS ( SELECT 1 
  6      FROM ACCT B 
  7      WHERE B.ACCTNBR = A.ACCTNBR 
  8        AND EXISTS ( SELECT 2 
  9         FROM ACCT C 
 10         WHERE C.ACCTNBR = A.ACCTNBR ) ) ;

Explained.

SQL> @xpl3
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3601810708

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    13 |     9   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL | ACCT |     1 |    13 |     3   (0)| 00:00:01 |
|*  3 |   FILTER            |      |       |       |            |          |
|*  4 |    TABLE ACCESS FULL| ACCT |     1 |    13 |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| ACCT |     1 |    13 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ACCT" "B" WHERE  EXISTS
              (SELECT /*+ */ 0 FROM "ACCT" "C" WHERE "C"."ACCTNBR"=:B1) AND
              "B"."ACCTNBR"=:B2))
   2 - filter("A"."ACCTNBR"=5)
   3 - filter( EXISTS (SELECT /*+ */ 0 FROM "ACCT" "C" WHERE
              "C"."ACCTNBR"=:B1))
   4 - filter("B"."ACCTNBR"=:B1)
   5 - filter("C"."ACCTNBR"=:B1)

Note
-----
   - dynamic sampling used for this statement

The references to "A"."ACCTNBR" are replaced by constant value :B1 and :B2.

Regards
Michel
Re: Error "ORA-00904" while using subquery. [message #574491 is a reply to message #574456] Fri, 11 January 2013 03:34 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
You are right, thanks!

PS. What happened to the speller checker ?
Re: Error "ORA-00904" while using subquery. [message #574494 is a reply to message #574491] Fri, 11 January 2013 05:09 Go to previous message
Michel Cadot
Messages: 59180
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is on the Reply not on the Quick Reply.

Regards
Michel
Previous Topic: array value in single quote
Next Topic: UTL_RAW.CAST_TO_NUMBER PL/SQL: numeric or value error
Goto Forum:
  


Current Time: Tue Sep 23 05:27:02 CDT 2014

Total time taken to generate the page: 0.06192 seconds