Sub-query problem on Oracle 10g
- by Eric
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.
What's the problem?How can I make it work?
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
)
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 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
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
