Delete SQL problem using Ora 7.0

I am using this stmt:

delete from revhistmp where object_name IN (select revhiscmp.* from revhiscmp where revhistmp.object_name = revhiscmp.object_name) and object_id IN (select revhiscmp.* from revhiscmp where revhistmp.object_id = revhiscmp.object_id);

In order to create revhistmp I used:

create table revhistmp as
 select object_id, object_name, object_rev from object;

In order to create revhiscmp I used:

create table revhiscmp as
 select object_id, object_id, object_rev from object where object_status = 'CMP'; The idea is to have a table of all objects and a table of closed or completed
objects. The resulting table using the delete or minus command needs to contain only the open objects. So I
used the:

create table revhis as
select * from revhistmp /* table that contains all the objects */ minus
select * from revhiscmp; /* table that contains all the completed/closed objects */  

But the data is not correct. I have a situated where:

object_id      object_name           object_status      object_rev
NNNNNN      NNNNNN                    cre                      1


   "                      "                          edt                  


   1


   "                      "                          dsp                  


  1


   "                      "                          sup                  


  2


   "                      "                           edt                 


   2


   "                      "                           dsp                 


  2


   "                      "                           wfc                 


   2


   "                      "                           cmp                 

Each object_id/object_name combo contains several steps. If the object has a CMP status then the complete record needs to be discarded, not just the
CMP entry for the object. Using the minus is just deleting CMP record and not the entire record thus we have closed objects that showing up as open objects. Any ideas what I should do? Or what I am doing wrong? For some reason I am unable to see what the problem is.

Thanks for the help,. Please email me at my work address: faha_at_eci-esyst.com

Felicia Received on Tue May 23 1995 - 00:00:00 CEST