Oracle Rownum = 1升级到12.1后不再返回结果

嗨，
我有一个在11g中创建的视图，在我们将数据库升级到12.1之后，它不再返回视图的日期。我为视图拉了SQL，并确认它不再起作用。它确实返回了评论。我需要使用rownum = 1，因为我可以有相同的事件，相同的日期不止一次。该视图实际上包含超过72个日期和注释。

所有有关此行为的帮助都将改变，并提供解决方案。谢谢。

SELECT APP.APPL_ID_SEQ
   -- FORM RECEIVED
   , (SELECT DISTINCT TO_CHAR(MAX(EVDT.DT_OF_EVNT), 'YYYY-MM-DD')
      FROM WRD_EVENT_DATES EVDT
      WHERE EVDT.APPL_ID_SEQ = APP.APPL_ID_SEQ
         AND ROWNUM = 1
         AND EVDT.EVTP_CD IN('FRMR')) AS RECD_DATE

   , (SELECT DISTINCT EVDT.EVNT_COMT
      FROM WRD_EVENT_DATES EVDT
      WHERE EVDT.APPL_ID_SEQ = APP.APPL_ID_SEQ
         AND ROWNUM = 1
         AND EVDT.EVTP_CD IN('FRMR')
         AND EVDT.DT_OF_EVNT = (SELECT MAX(EVDT.DT_OF_EVNT)
                                FROM WRD_EVENT_DATES EVDT2
                                WHERE EVDT2.APPL_ID_SEQ = APP.APPL_ID_SEQ
                                AND EVDT.EVTP_CD IN('FRMR')) ) AS RECD_COMMENT
FROM WRD_APPLICATIONS APP         
复制

这听起来像是一个错误，但无论如何，我会考虑用

SELECT APP.APPL_ID_SEQ
   -- FORM RECEIVED
   , (SELECT TO_CHAR(MAX(EVDT.DT_OF_EVNT), 'YYYY-MM-DD')
      FROM WRD_EVENT_DATES EVDT
      WHERE EVDT.APPL_ID_SEQ = APP.APPL_ID_SEQ
         AND EVDT.EVTP_CD IN('FRMR')) AS RECD_DATE
   , (SELECT EVDT.EVNT_COMT
      FROM WRD_EVENT_DATES EVDT
      WHERE EVDT.APPL_ID_SEQ = APP.APPL_ID_SEQ
         AND ROWNUM = 1
         AND EVDT.EVTP_CD IN('FRMR')
         AND EVDT.DT_OF_EVNT = (SELECT MAX(EVDT.DT_OF_EVNT)
                                FROM WRD_EVENT_DATES EVDT2
                                WHERE EVDT2.APPL_ID_SEQ = APP.APPL_ID_SEQ
                                AND EVDT.EVTP_CD IN('FRMR')) ) AS RECD_COMMENT
FROM WRD_APPLICATIONS APP        
复制

即，

-第一个子选择是最大值，因此无论如何您只能获得1行
-第二个子选择不需要DISTINCT，因为无论如何您都会获得一行 (通过ROWNUM)

另一件事是，如果所有这些子选择都基于相同的条件，则最好使用一种类型来运行一次查询，然后从那里提取，例如

select 
  ...,
  details.attrib1,
  details.attrib2,
  details.attrib3
from 
(
select
  ...,
  ( select my_object_type(col1,col2,col3,col4)
    FROM WRD_EVENT_DATES EVDT
      WHERE EVDT.APPL_ID_SEQ = APP.APPL_ID_SEQ
         AND ROWNUM = 1
         AND EVDT.EVTP_CD IN('FRMR')
         AND EVDT.DT_OF_EVNT = (SELECT MAX(EVDT.DT_OF_EVNT)
                                FROM WRD_EVENT_DATES EVDT2
                                WHERE EVDT2.APPL_ID_SEQ = APP.APPL_ID_SEQ
                                AND EVDT.EVTP_CD IN('FRMR')) as details
FROM WRD_APPLICATIONS APP 
)
复制