Hi.

I need to delete all ad-hoc groups for Accounts and Contacts.
I was thinking on this:


delete from sysdba.ADHOCGROUP
where ENTITYID in (select CONTACTID from sysdba.CONTACT)
or ENTITYID in (select ACCOUNTID from sysdba.ACCOUNT);

delete from sysdba.PLUGIN
where TYPE = 8
and (FAMILY like 'Contact' or FAMILY like 'Account')
and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP);



The strange thing (at least to me) is that when I get the count for both statements, the numbers are very different:

select COUNT(*)
from sysdba.PLUGIN
where TYPE = 8
and (FAMILY like 'Contact' or FAMILY like 'Account')
and PLUGINID in (select GROUPID from sysdba.ADHOCGROUP);
==> Result: 132

select COUNT(*)
from sysdba.ADHOCGROUP
where ENTITYID in (select CONTACTID from sysdba.CONTACT)
or ENTITYID in (select ACCOUNTID from sysdba.ACCOUNT);
==> Result: 13688


Do you think the two delete sentences above will correctly delete the ad-hoc groups for Accounts and Contacts?

Regards,

   Marcos

Marcos:

  Is not necessarily that the Numbers are wrong, but the order of the Statements:

This is what you are doing:

  a) Delete Data from AdhocGroups table for Accounts and Contact Entitties

  b) Deleting Plugin records based on Data From Adhoc Groups (which you have already Deleted)

So, just reverse the Order of your Statements:

  a) Delete from Plugin All Groups for Account and Contacts that have data listed under the AdhocGroup Table (this will remove 99% of the Adhoc groups - There is always a Chance that you have Adhoc Groups with No data associated with Them)

  b) Delete Data from AdhocGroups table

Also, you may need a better understanding of how AdhocGroups works specially when it comes to Releases and Copies.

There are 3 fields on the Plugin Table that are of Importance when Dealing with Groups:

PLUGINID, BASEDON and DATACODE.   *The DataCode column is the ID that is used to query data from the AdhocGroup Table.

If you create an Adhoc Group, the PluginID will be the GroupID, the BasedOn will be Null and the DataCode will the GroupID. 

If you Release this group a new Record (or multiple Records) will be created. Each Release Record will have its own GroupID. The BasedOn and DataCode fields will have the GroupID of the Original Group.

If you make a Copy of this group, the BasedOn should be Blank (as it is not a Release), but the DataCode remains pointing to the Original Group (this is based on my observation, and may be a bug).

What this means is that based on the SQL Statements that you had created, you may end up leaving a Bunch of Release Copies of the AdHoc Groups still available (although with no records to be displayed).

So, the best approach may be as follows:

a) Delete the Plugin records

DELETE FROM PLUGIN WHERE TYPE = 8 AND FAMILY IN ('Account', 'Contact')

(DATACODE IN (SELECT GROUPID FROM ADHOCGROUP) OR PLUGINID IN (SELECT GROUPID FROM ADHOCGROUP))

You may want to consider using this Statement instead:

DELETE FROM PLUGIN WHERE TYPE = 8 AND FAMILY IN ('Account', 'Contact') AND DATACODE <> ''

b) Delete the Records from the AdhocGroup Table

I would suggest you just do a cleanup of this table. Since you deleted the Plugin Records, just go for:

DELETE FROM ADHOCGROUP WHERE GROUPID NOT IN (SELECT PLUGINID FROM PLUGIN)

Finally, there are many reasons why your numbers don't match, but the most likely one is Orphaned Data.

It is quite Common to find Records on the AdhocGroup table where the GroupID doesn't match any Existing plugins records.

Thanks for your help Raúl.

Finally, I executed the following SQL statements:

--
-- Delete adhoc groups from the PLUGIN table
--
-- The DATACODE column is the ID that is used to query data from the ADHOCGROUP table.
--
DELETE FROM PLUGIN
WHERE TYPE = 8
AND FAMILY IN ('Account', 'Contact')
AND (DATACODE IN (SELECT GROUPID FROM ADHOCGROUP) OR PLUGINID IN (SELECT GROUPID FROM ADHOCGROUP))

--
-- Clean up the ADHOCGROUP table
--
DELETE FROM ADHOCGROUP WHERE GROUPID NOT IN (SELECT PLUGINID FROM PLUGIN)

Regards,

       Marcos