All geek questions in one place

The problem with additional queries on Oracle 10g

The following query works with Oracle 10.2.0.1.0 on Windows, but does not work with 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 removing the subquery. I found that if you use the T fields in a subquery, an error occurs. Does he say that the subquery cannot access the fields in the main query?

What is the problem? How can I make it work with oracle on linux? Thank!

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 -
CORE 10.2.0.1.0
TNS 32- Windows: 10.2.0.1.0 -
NLSRTL 10.2.0.1.0 -

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

+3
2

!!! http://forums.oracle.com/forums/thread.jspa?messageID=4023215?

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

script :

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

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

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

+3

, . T , .

, :

, , :

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
+4

Source: https://habr.com/ru/post/1736609/

More articles:


All Articles