Sub-query problem on Oracle 10g

Question

The following query works on Oracle 10.2.0.1.0 on windows,but doesn't work on Oracle 10.2.0.2.0 on Linux.

Error report: SQL Error: ORA-00904: "T"."AUDIT_USECS": invalid identifier 00904. 00000 - "%s: invalid identifier"

It works after i remove the sub-query. I found that if use fields of T in sub-query,then error occurs. Is it saying that sub-query can't access the fields in main query?

What's the problem?How can I make it work on oracle on linux? Thanks!

---

CREATE TABLE AUDITHISTORY(
CASENUM numeric(20, 0) NOT NULL,
AUDIT_DATE date NOT NULL,
USER_NAME varchar(255) NULL,
AUDIT_USECS numeric(6, 0) NOT NULL,
TYPE_ID INT NOT NULL    )

Query:

SELECT T.CASENUM,
       T.USER_NAME,
       T.AUDIT_DATE AS STARTED,
       (SELECT * 
          FROM (SELECT S.AUDIT_DATE 
                  FROM AUDITHISTORY S 
                 WHERE S.CASENUM=T.CASENUM AND TYPE_ID=2
                   AND S.USER_NAME=T.USER_NAME 
                   AND (S.AUDIT_DATE > T.AUDIT_DATE OR (S.AUDIT_DATE = T.AUDIT_DATE AND S.AUDIT_USECS > T.AUDIT_USECS))
              ORDER BY S.AUDIT_DATE ASC,S.AUDIT_USECS ASC
       ) WHERE rownum <= 1) AS ENDED
FROM AUDITHISTORY T WHERE TYPE_ID=1

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Answer 1

That is a bug!!! Check this Link http://forums.oracle.com/forums/thread.jspa?messageID=4023215?

or http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1853075500346799932#1859169400346361423

It is a bug only in Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

The following script works:

SQL> select * from AUDITHISTORY;

CASENUM AUDIT_DAT USER_NAME            AUDIT_USECS    TYPE_ID

---

    10 12-MAR-10 USER                         100          1
    10 14-MAR-10 USER                         100          2
    10 16-MAR-10 USER                         100          2

SQL> SELECT T.CASENUM,
  2  T.USER_NAME,
  3  T.AUDIT_DATE AS STARTED,
  4  (
  5      SELECT max(S.AUDIT_DATE) keep (dense_rank first order by S.AUDIT_DATE ASC,S.AUDIT_USECS ASC)
  6      from AUDITHISTORY S  
  7      WHERE S.CASENUM=T.CASENUM AND TYPE_ID=2
  8      AND S.USER_NAME=T.USER_NAME
  9      AND (
 10          S.AUDIT_DATE > T.AUDIT_DATE OR (
 11              S.AUDIT_DATE = T.AUDIT_DATE 
 12              AND S.AUDIT_USECS > T.AUDIT_USECS
 13          )
 14      )
 15  ) as ended
 16  FROM AUDITHISTORY T WHERE TYPE_ID=1;

CASENUM USER_NAME STARTED ENDED

---

    10 USER                 12-MAR-10 14-MAR-10

For more:http://forums.oracle.com/forums/thread.jspa?messageID=4160559#4160559

Answer 2

I consider it surprising that it works anywhere. You are using the alias T in the inline view, but it is only defined in the outer select.

UPDATE after reviewing all the information linked to in comments and other answers:

• The fact that it actually works in one/some version was a bug in that version according to Tom Kyte, which is a quite reliable source for this kind of information: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1853075500346799932#1859169400346361423
• There are two things at work here:
  1. When using a subselect as an inline view you can not reference anything of the outer select. See last bullet point in this document: http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/queries007.htm
  2. In other uses of subselects (in the select clause or in the where clause) you can reference stuff from the outer select, but only from the directly enclosing select. (I personally think this is a stupid restriction, but I guess nobody in the ANSI SQL commitees cares about my opinion)

Assuming all this is correct, this statement might work:

SELECT T.CASENUM,
   T.USER_NAME,
   T.AUDIT_DATE AS STARTED,
   (SELECT * 
      FROM (SELECT S.AUDIT_DATE 
              FROM AUDITHISTORY S 
             WHERE S.CASENUM=T.CASENUM AND TYPE_ID=2
               AND S.USER_NAME=T.USER_NAME 
          ORDER BY S.AUDIT_DATE ASC,S.AUDIT_USECS ASC
   ) R WHERE (S.AUDIT_DATE > T.AUDIT_DATE OR (S.AUDIT_DATE = T.AUDIT_DATE AND S.AUDIT_USECS > T.AUDIT_USECS))
   AND rownum <= 1) AS ENDED
FROM AUDITHISTORY T WHERE TYPE_ID=1