Hi,
When i tried to migrate from oracle 11g to 12C, years back, i had faced an issue which seems to be a bug with Oracle 12 C.
The following query was giving different result in Oracle 11g and 12 C (Sample Data and details are attached).
with dat as
(
select
a.empid,
a.badgeno,
b.fromdatetime ,
b.todatetime ,
b.authtype ,
rownum as pkey
from
empmst a,
authorisations b
where
a.empid=b.empid
)
, rep as
(
select
a.badgeno,
a.fromdatetime ,
a.todatetime ,
a.authtype,
b.fromdatetime fromdatetime1,
b.todatetime todatetime1,
b.authtype authtype1
from
dat a ,
dat b
where
a.pkey<>b.pkey and
a.empid=b.empid and
a.fromdatetime<b.todatetime and
a.todatetime>b.fromdatetime
)
, res as (
select * from rep
where
(authtype='DT' and authtype1='OT') or
(authtype='OT' and authtype1='DT')
)
select BADGENO,fromdatetime,todatetime,authtype,fromdatetime1,todatetime1,authtype1
from rep minus
select BADGENO,fromdatetime,todatetime,authtype,fromdatetime1,todatetime1,authtype1
from res;
Please help me with this issue.
oracle 12c error.pdf
Thank You