SQL & PL/SQL

0

I have written a query to take compare of columns/data of 2 database table in oracle which will print records where there is any difference in rows 2 tables based on key columns (fields in partition by clause) and if there is no difference then for any particular field then it will print null and for fields where there is difference then it will print there values (where row_cnt = 2) and if there is some extra record in any of table then it also print it (where row_cnt =1).

Sample script --

create table src_tab (id number,name varchar2(10), job varchar2(10), sal number);

create table tgt_tab (id number,name varchar2(10), job varchar2(10), sal number);

insert into src_tab values (1, 'john', 'not sure', 100);
insert into src_tab values (2, 'kevin', 'unemp', 200);
insert into src_tab values (3, 'chad', 'emplyd', 400);
insert into src_tab values (4, 'page', 'NA', 500);
insert into src_tab values (5, 'emmy', 'desk', 600);

insert into tgt_tab values (1, 'john', 'not sure', 150);
insert into tgt_tab values (2, 'kevin', 'unemp', 200);
insert into tgt_tab values (3, 'chad', 'empl', 400);
insert into tgt_tab values (4, 'page', 'NA', 500); 

Comparison query --
Select * from (select id,name,
  case NEW_CNT when 1 then 'TGT_TAB' else 'SRC_TAB' end tbl,
  ROW_CNT,
  case when count(job) over(partition by id,name)
    between 1 and count(distinct job) over(partition by id,name)
    then job end job,
  case when count(sal) over(partition by id,name)
    between 1 and count(distinct sal) over(partition by id,name)
    then sal end sal
FROM (
  select
    id,name, job, sal,
    sum(NEW_CNT) NEW_CNT, count(*) over(partition by id,name) ROW_CNT
  FROM (
    select 
    id,name, job, sal,
    -1 NEW_CNT
    from src_tab O
    union all
    select
    id,name, job, sal,
    1 NEW_CNT
    from tgt_tab N 
  )
  group by
    id,name, job, sal
  having sum(NEW_CNT) != 0
)
order by 1, 2, new_cnt)
;

Since this query is written for oracle 19c database now I require to run this query on Sybase database but when I tried it on Sybase then I found out that COUNT(DISTINCT) windowing function is not allowed there so now looking for alternative ways to print the same output as it is on Sybase I tried using lead/lag function since it is supported on Sybase but the output is not good.

Alternative Tried --
case when sal <>
    lead(sal,1,sal) over (partition by id,name order by id) then sal  end sal_lead

Expected Result --    
ID  NAME  TBL      ROW_CNT JOB    SAL 
1   john  SRC_TAB   2      null   100 
1   john  TGT_TAB   2      null   150 
3   chad  SRC_TAB   2      emplyd null 
3   chad  TGT_TAB   2      empl   null 
5   emmy  SRC_TAB   1      desk   600

Please let me know if more information is required from my side to understand the query I have written in oracle.