IM InfoSphere Identity Insight, Version 8.0

Total Unique Numbers by Entity query

This query returns information about how many different unique numbers are associated with a particular entity, by Entity ID. You might find this query useful if each entity typically only has one unique number. Checking to see if entities contain many different types of unique numbers is an excellent way to check for data anomalies and verify that your resolution rules are working as expected.

Total Number of Unique Numbers Associated with a Single Entity SQL Query statement

select	distinct *
from
	(select entity_id,
		(select count(distinct num_value)
		from
			nums,
			num_type
		Where
			nums.num_type_id=num.type.num_type_id
			and num_type.unique_FLAG='Y'
			and nums.entity_id=dsrc_acct.entity_id
		) as UNIQUE_NUMBER_CNT
	from dscr_acct
	)as tab1
where
	UNIQUE_NUMBER_CNT>1
order by
	UNIQUE_NUMBER_CNT DESC;

What next?

In the Visualizer, use the Find by Entity ID screen to look up the Entity IDs returned from the Total Number of Unique Numbers by Entity ID query results. By reviewing the entity resume for each entity, you can determine if the entity should have more than one unique number. In some cases, this situation might be an indication of fraud. For example, in the United States Social Security Numbers (SSNs) are unique numbers. Typically, each U.S. entity only has one SSN. If this query uncovers an entity that has multiple SSNs, the next step is probably to do further investigation and analysis of why the entity has multiple SSNs.

Example results of Total Number of Unique Numbers by Entity query

Here is an example of what the results of running the Total Number of Unique Number by Entity query might look like:

ENTITY_ID UNIQUE_NUMBER_CNT
3003 2
3030 2
3039 2


Feedback

Last updated: 2009