Sub-query problem on Oracle 10g

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

Solution

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