SQL & PL/SQL

Hi,

I am trying to migrate data from oracle 11g to 12C.

We are using

Oracle Database 11g Release 11.2.0.4.0 - 64bit

Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit

My software is not working as expected in Oracle 12C.

For eg:

I have following two tables.

EMPMST

create table empmst  as  select 100 empid, 'C1001' badgeno,'Ganesh' empname from dual;

AUTHORISATIONS

create table authorisations  as

select 100 empid,'05/03/2019 16:00' fromdatetime,'05/03/2019 20:00' todatetime,'OT' authtype,'Overtime' authdescription from dual union all

select 100 empid,'05/03/2019 16:00' fromdatetime,'05/03/2019 20:00' todatetime,'DT' authtype,'Official Duty' authdescription  from dual;

I am trying to retrieve data with following query

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  
                    )   
select * from rep 
          where   
                    (authtype='DT' and authtype1='OT') or    
                    (authtype='OT' and authtype1='DT')   

The above query gives different results in Oracle 11g and 12C.

Result in 11g (which is expected)

Result in 12C

I know that it is possible to get same result in both 11g and 12C with slight modification in query. But it is not easy to check and modify  all queries used in software.

I would like to know whether i can migrate existing data to 12C without any modification in queries.

Please help