If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Sybase > How to use Minus operation in Sybase

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 3
Red face How to use Minus operation in Sybase

Hi,

I have table 1 as T1 and its backup copy as T2, both having same structure, in oracle its easy to do a minus operation by select * from T1 minus select * from T2 to get the differences in both the tables

but i tried hard to find same operation in Sybase as sybase doesnt supports minus operation.

Could anyone suggests how should i see the difference between both the tables in sybase, its quite urgent...

Thanks
Aman
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: The Hague/Utrecht, NL
Posts: 503
You'll need a primary key/unique identifier (i.e. ID):

select * from T1 where ID not in (select ID from T2)

This displays all records from T1 that have an ID that's not present in T2.
__________________
I'm not crazy, I'm an aeroplane!
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 3
I have this kind of query where

select col1, col2 from T1 where
not exists
(select 1 from T2 where T1.col1=T2.col2 and T1.col1=T2.cpl2)

but this works only in case of tables having few columns but not in case of large tables having more columns.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: The Hague/Utrecht, NL
Posts: 503
What is the purpose of T2? Is it an unaltered copy of T1, but perhaps with fewer records? Or is the data in T2 altered?
If it's the first, you can use my first example. You'll only need to compare unique identifiers because the other columns haven't changed.

If you're dealing with situation 2, you will have to compare every column, which is quite an endeavor. And it will probably wreak havoc on your performance.

Also: why do you want to compare the two tables?
__________________
I'm not crazy, I'm an aeroplane!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 3
T1 is the altered table having new records while the T2 table is the baselined table, actually this I want to do for Regression where I want to know if any new records added due to program run so thats why i want to do minus operation.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Location: The Hague/Utrecht, NL
Posts: 503
In that case I think you will have to create giant where-clause, compairing every column...
__________________
I'm not crazy, I'm an aeroplane!
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 96
There is another way to get the difference of the tables' content.
Take the BCP out of both the tables in files T1.txt and T2.txt
and u can find the difference b/w them using diff command of unix.
ie on UNIX prompt execute diff T1 T2
Reply With Quote
Reply

Tags
difference operator, sybase

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On