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?
Sam OZAsked:
Who is Participating?
 
Mark GeerlingsDatabase AdministratorCommented:
I think this query for Oracle will give you what you are asking for:

select d.DocumentNo, d.DocRevision, d.ItemNo, i.ItemName, count(d.location) Qty
from Documents d, Items I
where i.ItemNo = d.ItemNo
group by d.DocumentNo, d.DocRevision, d.ItemNo
having count(d.location) > 1;

Open in new window


You could add this after the "Qty" if you want to have the location values returned also:
, min(d.location) "Min_loc", max(d.location) "Max_loc"

Open in new window

0
 
flow01Commented:
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;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.