OraFAQ Forum: SQL & PL/SQL » find different or missing rows in one table

Home » SQL & PL/SQL » SQL & PL/SQL » find different or missing rows in one table (Oracle, 11.2, Windows 2008R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

find different or missing rows in one table [message #619983]

Sun, 27 July 2014 06:58

forumwurm
Messages: 12
Registered: March 2014

Junior Member

Hi all

I'm new to Oracle and SQL statements. Could somebody please tell me how I can achieve the following:

I have a table in Oracle and should find the rows which are different between Version OLD and Version New (which is a field as well)

Version Field1 Field2 Field3 Field4 Value
OLD Hypo abcd 1234 something 1
NEW Hypo abcd 1234 something 1
OLD Hypo abcd 1234 something 2
NEW Hypo abcd 1234 something 2
OLD Hypo abcd 1234 something 3
NEW Hypo abcd 1234 something 3
OLD Hypo abcd 1234 something 4
NEW Hypo abcd 1234 something 4
OLD Hypo abcd 1234 something 5
NEW Hypo abcd 1234 something 5div
OLD Hypo abcd 1234 something 6
NEW Hypo abcd 1234 something 6
OLD Hypo abcd 1234 something 7
NEW Hypo abcd 1234 something 7
OLD Hypo abcd 1234 something 8

In this example I should find two lines:

- 5div is different
- and value 8 is missing in the NEW version.

which SQL statement will bring me this output?

Many thanks for links, tips, or a statement

FW

Report message to a moderator

Re: find different or missing rows in one table [message #619984 is a reply to message #619983]

Sun, 27 July 2014 07:10

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

You need to explain how to identify the pairs of rows that should be compared. It looks as though they are keyed on field1||field2||field3||field4. Can you guarantee that a search on those columns will always return exactly two rows, where the column version is either OLD or NEW?

And please use [code] tags to format data, as described here How to use [code] tags and make your code easier to read

Report message to a moderator

Re: find different or missing rows in one table [message #619991 is a reply to message #619983]

Sun, 27 July 2014 07:46

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

forumwurm wrote on Sun, 27 July 2014 17:28

In this example I should find two lines:

- 5div is different
- and value 8 is missing in the NEW version.

which SQL statement will bring me this output?

You need a set operator, MINUS.

Something like,

WITH old_ver 
     AS (SELECT * 
         FROM   table 
         WHERE  version = 'OLD'), 
     new_ver 
     AS (SELECT * 
         FROM   table 
         WHERE  version = 'NEW') 
SELECT * 
FROM   new_ver 
MINUS
SELECT * 
FROM   old_ver

Note : The above code is not tested, it's just an example.

Report message to a moderator

Re: find different or missing rows in one table [message #619994 is a reply to message #619991]

Sun, 27 July 2014 07:55

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I was thinking of

select max(version),field1,field2,field3,field4,value,count(*) from whatever
group by field1,field2,field3,field4,value having count(*)=1;

I'm sure there are other solutions too.

Report message to a moderator

Re: find different or missing rows in one table [message #620013 is a reply to message #619994]

Sun, 27 July 2014 16:28

forumwurm
Messages: 12
Registered: March 2014

Junior Member

hi all

thanks for your tips.

@ john, sorry for not using the code syntax. could not find an option to edit my first post.

Quote:

It looks as though they are keyed on field1||field2||field3||field4. Can you guarantee that a search on those columns will always return exactly two rows, where the column version is either OLD or NEW?

Correct, it is like you describe here.

@ Lalit, this did not give me the result I'm looking for.

@ john, the statement "select max (version)", etc. did give me the result in a test table, but in the real table I get the message out of memory ;(

It is about 55 columns and 300'000 records for one month selection. is there an other way which will handle this amount of data. not sure if I can get the memory issue solved.

Report message to a moderator

Re: find different or missing rows in one table [message #620014 is a reply to message #620013]

Sun, 27 July 2014 16:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ERROR? What Error?
I don't see any error.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: find different or missing rows in one table [message #620015 is a reply to message #620014]

Sun, 27 July 2014 16:57

forumwurm
Messages: 12
Registered: March 2014

Junior Member

[Err] Out of memory

that's all i get in Navicat. Don't think that I get more information in SQL Developer, but I can try tomorrow

Report message to a moderator

Re: find different or missing rows in one table [message #620016 is a reply to message #620015]

Sun, 27 July 2014 17:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>[Err] Out of memory
from brain dead GUI client; not from Oracle.
The results will likely be different when running SELECT from sqlplus client which is installed with every Oracle database.

Report message to a moderator

Re: find different or missing rows in one table [message #620025 is a reply to message #620013]

Mon, 28 July 2014 00:07

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

@ Lalit, this did not give me the result I'm looking for.

We have not your tables and data.
If you want specific query valid for your case you must post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data.

Report message to a moderator

Previous Topic:	SQL
Next Topic:	why this code with error?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:52:03 CDT 2024