problem with minus operator ...

bravegag · *nix forums beginner Joined: 12 Apr 2006 Posts: 16

Hi all,

I can't help but to get continously frustrated with Sybase i.e.
something fairly simple as:

select *
from table_1
minus
select *
from copy_of_table_1

works but does not give me the needed results ... guess what, the
order is missing:

select *
from table_1
order by the_pk
minus
select *
from copy_of_table_1
order by the_pk

Produces: Number (102) Severity (15) State (1) Server ... Incorrect
syntax near 'minus'

I get the same or other errors if I try:

(select *
from table_1
order by the_pk)
minus
(select *
from copy_of_table_1
order by the_pk)

or

select *
from (select * from table_1 order by the_pk)
minus
select *
from (select * from copy_of_table_1 order by the_pk)

etc etc

I am praying for the project to be moved to Oracle ... Sybase sucks!

Is there any chance that I a doing something wrong here? I dont think
so ... but who knows ...

Thanks in advance,
Best Regards,
Giovanni

ZeldorBlat · *nix forums Guru Wannabe Joined: 01 Mar 2005 Posts: 272

bravegag wrote:

bravegag · *nix forums beginner Joined: 12 Apr 2006 Posts: 16

ZeldorBlat wrote:

Carl Kayser · *nix forums beginner Joined: 25 May 2005 Posts: 42

"bravegag" <bravegag@gmail.com> wrote in message
news:1150993229.342528.105770@m73g2000cwd.googlegroups.com...

Mark A. Parsons · *nix forums beginner Joined: 18 May 2005 Posts: 17

I'm not familiar with the 'minus' operator ... at least not in Sybase ASE.

What version of the Sybase dataserver are you running? (select @@version)

In the meantime, if your objective is pull everything from table_1 which
does not exist in copy_of_table_1 (based solely on a primary key) ... and
assuming your primary key is a column called 'pk' ... try:

select t1.*
from table_1 t1
where not exists(select c1.pk
from copy_of_table_1 c1
where c1.pk = t1.pk)

If on the other hand you have matching pk's in both tables but you're
looking for differences in individual column values ... you could try
something like:

select t1.*
from table_1 t1
where not exists(select c1.pk
from copy_of_table_1 c1
where c1.pk = t1.pk
and c1.col1 = t1.col1
and c1.col2 = t1.col2
and ...)

-----------------------

As for the comment about 'Sybase sucks' ... *shrug* ... I know of
situations where Oracle sucks ... MSSQL sucks (different from MS OS's suck!
Wink

... DB2 sucks ... and on and on and on and on ...

If you have a background of working with one product (eg, Oracle) and then
find yourself having to start over from (relative) scratch with another
product (eg, Sybase) ... yeah, the switch itself sucks ... and learning the
differences between PL/SQL and T-SQL will suck ... and if you're not
able/willing to take the time to learn how to use the new product properly
then life in general will appear to suck ...

I've found that Oracle has a lot of features that make life easier for the
developer but (somewhat) tougher for the DBA ... while Sybase features tend
to make life easier for the DBA while requiring the developer to do a bit
more work ... ymmv ...

bravegag wrote:

--CELKO-- · *nix forums Guru Wannabe Joined: 01 Jun 2005 Posts: 122

MINUS is a proprietary Oracle syntax. The Standard SQL version is
EXCEPT [ALL}. Since tables have no ordering by definition, parts of
this posting make no sense. This is SQL and not sequential tape file
merging.

Google