KCL Software Solutions

Find Public Records with Non-Public/Deleted Equivalent

Note: an "equivalent record" is an associated version of the same record in another language

Community Information Records with Mismatched Public Status

To find a record that has an equivalent record that has a different public status than the current record:

EXISTS(SELECT * FROM GBL_BaseTable_Description btdx
WHERE btdx.NUM=bt.NUM AND btdx.LangID<>btd.LangID
AND btdx.NON_PUBLIC<>btd.NON_PUBLIC
AND btdx.DELETION_DATE IS NULL)

This query works in either direction - if the current record is non-public, it will match against public records, and vice versa. Therefore you can search in either language to retrieve meaningful results. This query does not match if the equivalent record is deleted.

Community Information Records with a Deleted Equivalent

Case 1: The current record is deleted, but it has an equivalent record that is not deleted (you must check the "Include Deleted Records" checkbox):

EXISTS(SELECT * FROM GBL_BaseTable_Description btdx
WHERE btdx.NUM=bt.NUM AND btdx.LangID<>btd.LangID
AND btd.DELETION_DATE IS NOT NULL AND btdx.DELETION_DATE IS NULL)

Case 2: The current record is not deleted, but it has an equivalent record that is deleted:

EXISTS(SELECT * FROM GBL_BaseTable_Description btdx
WHERE btdx.NUM=bt.NUM AND btdx.LangID<>btd.LangID
AND btdx.DELETION_DATE IS NOT NULL AND btd.DELETION_DATE IS NULL)

Volunteer Opportunity Records with Mismatched Public Status

To find a record that has an equivalent record that has a different public status than the current record:

EXISTS(SELECT * FROM VOL_Opportunity_Description vodx
WHERE vodx.NUM=bt.NUM AND vodx.LangID<>vod.LangID
AND vodx.NON_PUBLIC<>vod.NON_PUBLIC AND vodx.DELETION_DATE IS NULL)

This query works in either direction - if the current record is non-public, it will match against public records, and vice versa. Therefore you can search in either language to retrieve meaningful results. This query does not match if the equivalent record is deleted.

Volunteer Opportunity Records with a Deleted Equivalent

Case 1: The current record is deleted, but it has an equivalent record that is not deleted (you must check the "Include Deleted Records" checkbox):

EXISTS(SELECT * FROM VOL_Opportunity_Description vodx
WHERE vodx.NUM=bt.NUM AND vodx.LangID<>vod.LangID
AND vod.DELETION_DATE IS NOT NULL AND vodx.DELETION_DATE IS NULL)

Case 2: The current record is not deleted, but it has an equivalent record that is deleted:

EXISTS(SELECT * FROM VOL_Opportunity_Description vodx
WHERE vodx.NUM=bt.NUM AND vodx.LangID<>vod.LangID
AND vodx.DELETION_DATE IS NOT NULL AND vod.DELETION_DATE IS NULL)

Did you find this article helpful?