Hi,
I have a view that I created in 11g, after we upgraded the database to 12.1 it is no longer returning the dates for the view. I pulled the SQL for the view and confirmed that it no longer works. It does return the comment. I need to use the rownum = 1 because I could have the same event, with the same date more than once. The view actually contains over 72 dates and comments.
All help on this behavior change and a solution is much appreciated. Thank you.
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
That sounds like a bug, but in any event, I would look at replacing the code with
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
ie,
- the first subselect is a MAX so you will only ever get 1 row anyway
- the second subselect does not need DISTINCT since you are getting a single row anyway (via ROWNUM)
The other thing is that if all of these subselects are based on the same criteria, you may better of using a type to run the query once, and then extract from there, eg
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
)