Hi Folks.

I just discovered a minor data inconsistency in a test database. (I believe I can prevent that in production.)  To check it I need to be able to run two queries and check the differenced between them.  I tried this:

select (yadayada) from <table-a> where (the usual)
minus
select (yadayada) from <table-b> where (the usual)
I get a syntax error on the minus.  Per ideas I gound in google, I also tried "except". Same syntax error.

To your knowledge, is there such an operator in release 12 to display what is inh the active set of query-a that is NOT in thqt of query-b?

A workaround just occurred to me: Unloading both queries (none too enormous) and diffing the files.  But is there a direct way, so I can correct what's missing from within SQL?  (Yes, I  know the unloaded diff could be loaded into table-b.)

Thanks.

------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------

The MINUS operator is supported in Informix 12.10, so maybe there is a genuine syntax error in you query.

Here is a dumb example:

select tabid,tabname from systables
where tabtype = "T"
  and tabid < 100
minus
select tabid,tabname from systables
where tabtype = "T"
  and tabid < 100
  and tabname not matches ("*tab*");

------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------

Both minus and except work correctly in v15 as well:

> select min(id) from extents;

      (min) 

     217329

1 row(s) retrieved.

> select * from extents where id < 217400
> minus 
> select * from extents where id >217320;

No rows found.

> select * from extents where id < 217400
> minus 
> select * from extents where id >217330;

dbsname  sysmaster 
tabname  sysams 
chunk    1
offset   8030
size     6
truth    t
id       217329

dbsname  sysmaster 
tabname  sysams 
chunk    1
offset   8030
size     6
truth    t
id       217330

2 row(s) retrieved.

> select * from extents where id < 217400
> except 
> select * from extents where id >217330;

dbsname  sysmaster 
tabname  sysams 
chunk    1
offset   8030
size     6
truth    t
id       217329

dbsname  sysmaster 
tabname  sysams 
chunk    1
offset   8030
size     6
truth    t
id       217330

2 row(s) retrieved.

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: 07/24/26 02:56 PM
From: Mike Walker
Subject: Minus operator in IDS Release 12?

The MINUS operator is supported in Informix 12.10, so maybe there is a genuine syntax error in you query.

Here is a dumb example:

select tabid,tabname from systables
where tabtype = "T"
  and tabid < 100
minus
select tabid,tabname from systables
where tabtype = "T"
  and tabid < 100
  and tabname not matches ("*tab*");

tabid    1

tabname  systables

 

tabid    4

tabname  systabauth

 

tabid    10

tabname  syssyntable

 

tabid    41

tabname  systabamdata

------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------