We are currently reviewing the code for 19c upgrade in our dev environments and i've found an issue which is working in older versions but errors out in the latest 19c version. Below is the testcase created. Please help out to resolve the issue and why would it work in previous versions but not in latest.
Database release:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
Testcase:
CREATE TABLE test1
( my_id VARCHAR2(30),
upd_dt DATE);
CREATE TABLE test2
( my_id VARCHAR2(12),
upd_dt DATE);
INSERT INTO test1 VALUES ('1234',SYSDATE);
INSERT INTO test1 VALUES ('5678',SYSDATE);
Below is the merge statement which fails. The error is while trying to compare date columns.
MERGE INTO test2 A
USING (
SELECT B.\*,
(CASE WHEN UPD\_DT = REP\_MAX\_UPD\_DT THEN 'A' ELSE 'E' END ) ACTION\_CD
FROM (
SELECT A.\*,
MAX(upd\_dt) OVER (PARTITION BY my\_id) REP\_MAX\_UPD\_DT
FROM (
SELECT A.my\_id,
A.upd\_dt
FROM test1 A
) A
)B
) B
ON (A.my_id = B.my_id)
WHEN MATCHED THEN UPDATE
SET A.UPD_DT = SYSDATE;
I see the below error when the merge is ran in line 4 of the statement. Adding to_date is resolving the issue (to_date(REP_UPD_DT,'DD-MON-YY') = to_date(REP_MAX_UPD_DT,'DD-MON-YY') ) but why is it working in previous versions and not in 19c is the question.
