Minus operator in IDS Release 12?
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 < 100minus
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
------------------------------
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
------------------------------