asked on

Oracle query to comapre rows

Hi Experts,
I have the requirement to find mismatch in some fields. I got the Sql server query in following link
https://www.experts-exchange.com/questions/29080853/Sql-Server-query-to-compare-rows.html
( The refined question with multiple tables is in my comments on the above link)

Can you please give me the query for Oracle?

flow01

create table Documents
(
      DocumentNo varchar2(10),
      DocRevision varchar2(10),
      Location varchar2(30),
      ItemNo varchar2(20)
);

insert into Documents
values
('Doc1', 'Rev1', 'Loc1', '001');
insert into Documents
values
('Doc1', 'Rev1', 'Loc1', '001');
insert into Documents
values
('Doc1', 'Rev1', 'Loc2', '001');
insert into Documents
values
('Doc2', 'Rev1', 'Loc2', '002');
insert into Documents
values
('Doc2', 'Rev1', 'Loc2', '002');

create table Items
(
      ItemName varchar2(30),
      ItemNo varchar2(20)
);

insert into Items
values
('Item Name 1', '001');
insert into Items
values('Item Name 2', '002');

with cte as
(
      select a.DocumentNo, a.DocRevision, a.Location, b.ItemName
      from Documents a inner join Items b
      on a.ItemNo = b.ItemNo
      group by a.DocumentNo, a.DocRevision, a.Location, b.ItemName
), cte2 as
(
      select ItemName, DocRevision
      from cte
      group by ItemName, DocRevision
      having count(*) > 1
)
select a.*
from cte a inner join cte2 b
on a.ItemName = b.ItemName and a.DocRevision = b.DocRevision;

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial