how to use union and minus in Sybase ?

I have to compare 2 Sybase tables table_A and table_B , they have the same collum  name, they maybe have different data.
what i am thinking is :
select name, department,... from table_A
minus
select name,department,... from table_B

but Sybase does not support minus ?!

is there any similar SQL in Sybase?


SayYou_SayMeAsked:
Who is Participating?
 
alpmoonConnect With a Mentor Commented:
Do you have a key column? Is it ASE?

If name is primary key, I think this should work:

select * from table_A A
where not exists (select * from table_B B
                             where B.name = A.name)
0
 
sridharv9Connect With a Mentor Commented:
As there is no minus operator available in ASE you can alternate option as above with "not exists" or "not in".
0
 
SayYou_SayMeAuthor Commented:
thanks guys

i have to compare all columns between tableA and tableB
just to compare the values in the tables if they are exactly the same
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sridharv9Connect With a Mentor Commented:
Try this query to find any difference between two tables. Replace .d,.c,.b with table columns.

SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (Table2.d = Table1.d) AND (Table2.c =
Table1.c) AND (Table2.b = Table1.b) AND (Table2.a = Table1.a) AND
(Table2.ID = Table1.ID)
WHERE (((Table1.ID) Is Null));
0
 
SayYou_SayMeAuthor Commented:
thanks guys

alpmoon--

your solution only works for checking one clumn Name, but i have to check all the fields are diferent .

another word, if the name is the same in both tables, it will show no result, whatever the other fields the same or not ( it do not match my request)

alpmoon--

your solution seems not work




0
 
alpmoonConnect With a Mentor Commented:
That was the reason I asked whether you have a primary key. If you need to compare all columns, you can add others in where clause:

select * from table_A A
where not exists (select * from table_B B
                             where B.name = A.name and B.department = A.department and ....)
0
 
SayYou_SayMeAuthor Commented:
thanks alpmoon

if there are more than 200 columns , is there any solution?
0
 
alpmoonConnect With a Mentor Commented:
Having a primary key or a better data model in general
0
 
SayYou_SayMeAuthor Commented:
I have 2 sybase tables
table_A
source, name, department,
New_York  Bill         IT
London        mike     HR
table_B
name,department
Bill   IT
Mike   HR

Table_B will have the data of New York or London

I want to create a stored procedure to compare the 2 tables if the values of columns are exactly the same and return a message


CREATE PROC data_chk
@source      VARCHAR(10)
as
begin
CREATE TABLE #t (source      VARCHAR(10)     null,
                   update_status            VARCHAR(100)    null)
select * from table_B a
where not exists (select * from table_A b
                  where isnull(a.name,' ')=isnull(b.name,' ')
                  and isnull(a.department,' ')=isnull(b.department,' ')
                  and a.source=@source)
if      @@rowcount = 0
  insert into #t
         values(@source,
                 'no update')
                 
                 
select      *
from      #t
return      0
end

I can run the stored procedure with no error, but I can not call it from Informatica, I will give error message
 --basically because it returna 2 result set
   One is :select * from table_B …
 One is select * from #T
But if  I  create the store procedure like this , it works
CREATE PROC data_chk
@source      VARCHAR(10)
as
begin
CREATE TABLE #t (source      VARCHAR(10)     null,
                   update_status            VARCHAR(100)    null)
/*select * from table_B a
where not exists (select * from table_A b
                  where isnull(a.name,' ')=isnull(b.name,' ')
                  and isnull(a.department,' ')=isnull(b.department,' ')
                  and a.source=@source)
if      @@rowcount = 0 */
  insert into #t
         values(@source,
                 'no update')
                 
                 
select      *
from      #t
return      0
end

question:
how to create this Sybase stored procedure to achieve my request?
0
All Courses

From novice to tech pro — start learning today.